Can anyone please help me?

I need to add the word "Core" above any cell that has the background color index 47.

The part I cannot get is in the loop, stating from top to bottom, the row counter minus one. This is what I have so far. The problem row seems to be the forth one.

Dim r as range

For each r in range("A1:A10").rows

If r.interior.colorindex=47 then

Cells(r-1,"A").value="Core"

End If

Next r

As you can see it seems simple but I am a beginner trying to learn it.

Thanks a lot

Desired output:

Print the Core word above any row with background color 47

CodePudding user response：

Here is an example of how you could achieve your desired output in Microsoft Excel VBA:

Sub AddCore()
  Dim r As Range

  For Each r In Range("A1:A10").Rows
    If r.Interior.ColorIndex = 47 Then
      Cells(r.Row - 1, "A").Value = "Core"
    End If
  Next r
End Sub

In this code, the AddCore() subroutine is defined and iterates over the rows in the range A1:A10. For each row, the code checks if the row's background color has a color index of 47. If it does, the code sets the value of the cell in the same column but one row above the current row to "Core".

CodePudding user response：

2 major errors:

1. The minimum row number of the range should be 2 because you want to print the word above the row.
2. r is a range object, you want to check a cell color index value so you should choose each of the cells of the range

Here´s the code after the correction:

Sub add_text_based_on_color()

Dim r As Range

For Each r In Range("A2:B10").Rows

    For Each cel In r.Cells
    
        If cel.Interior.ColorIndex = 47 Then
        
            Cells(cel.Row - 1, cel.Column).Value = "Core"

        End If
    Next cel
Next r


End Sub