Interior Colour Selection Query

**timbo1957** · 10-17-2011, 10:57 AM

Is it possible to have a macro scan the range A2:AF300 and if any cells have an interior color index number of 33 i.e.

    With Selection.Interior
        .ColorIndex = 33
        .Pattern = xlSolid
    End With

If it finds any cells then select the range A:AF of the row the cell is in and colour the whole row using the above code?

Thanks

Tim.

**Kenneth Hobson** · 10-17-2011, 12:04 PM

Sub ColorRowIfIC33()
  Dim cell As Range, rCell As Range
  For Each rCell In Range("A2:AF300").Rows
    For Each cell In rCell.Cells
      If cell.Interior.ColorIndex = 33 Then
        rCell.Interior.ColorIndex = 33
        GoTo NextrCell
      End If
    Next cell
NextrCell:
  Next rCell
End Sub

**timbo1957** · 10-18-2011, 04:34 AM

This works but in the wrong way.

Sub ColorRowIfIC33()
  Dim cell As Range, rCell As Range
  For Each rCell In Range("A2:AF300").Rows
    For Each cell In rCell.Cells
      If cell.Interior.ColorIndex = 33 Then
        rCell.Interior.ColorIndex = 33
        GoTo NextrCell
      End If
    Next cell
NextrCell:
  Next rCell
End Sub

I need it to start in row 2 and if any cell in A2:AF2 has an Interior Colour.Index of 33 then colour the whole of row 2 Interior Colour.Index of 33.

Then go to Row 3 and do the same.

What it is doing at the moment is coloring every row if any row in the range has a Interior Colour.Index of 33.

In addition the range is variable so maybe from A2 to the bottom of the data would be better.