0

I have an excel macro to color a row (from column A to E) " i ", starting from row 4 based on the value in "E(i)" ; the code goes like this :

Dim i As Integer
Dim arrSht, k
arrSht = Array("1. Asia", "2. Asia", "3. Asia", _
    "4. Asia", "6. Europe", "7. Europe")

For k = LBound(arrSht) To UBound(arrSht)
    With Worksheets(arrSht(k))
        For i = 4 To 11
            If Cells(i, 5).Value >= 0.25 Or _
                Cells(i, 5).Value <= -0.25 Then
            Range(Cells(i, 1), Cells(i, 5)).Interior.Color = 65535
            End If
        Next i
   End With
Next k

End Sub

The code works fine in coloring the rows, except that it only color the row for the active sheet. When I tried to put something like 

 Workbooks("Color row.xlsx").Sheets(arrSht(k)).Range(Cells(i, 1), Cells(i, 5)).Interior.Color = 65535

I get the "Run-time error '438': Object doesn't support this property or method" for that line. I don't know what should i put so that it colors the cell for each worksheets in the specified array.

Please help!

Thanks a lot :)

vbanoob27
  • 5
  • 1
  • 2

1 Answers1

0

Just slightly change your code like this:

Dim i As Integer
Dim arrSht, k
arrSht = Array("1. Asia", "2. Asia") ', "3. Asia", "4. Asia", "6. Europe", "7. Europe")

For k = LBound(arrSht) To UBound(arrSht)
    Worksheets(arrSht(k)).Activate
    With ActiveSheet
        For i = 4 To 11
            If Cells(i, 5).Value >= 0.25 Or _
                Cells(i, 5).Value <= -0.25 Then
            Range(Cells(i, 1), Cells(i, 5)).Interior.Color = 65535
            End If
        Next i
   End With
Next k

Notice that I have used Worksheets(arrSht(k)).Activate and then With ActiveSheet.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63