Fill Cells with Red

[Guest]

I have a spreadsheet that I need to fill the interior color of the row red if
column f contains the text red. I need the data in each row to turn red if f
is red for columns a through e and g through m.
Is there an easy way to do this other than sorting/filtering. I have
already used the conditional formatting option for another purpose. Is there
some
way to do this with VBA?

Any help is appreciated.

 

[Guest]

Sub cb()
n = Cells(Rows.Count, "F").End(xlUp).Row
For i = 1 To n
If Cells(i, "F").Value = "red" Then
Range("A" & i & ":E" & i).Interior.ColorIndex = 3
Range("G" & i & ":M" & i).Interior.ColorIndex = 3
End If
Next
End Sub

 

[Guest]

Thanks Gary. This is just what I needed.

Sub cb()
n = Cells(Rows.Count, "F").End(xlUp).Row
For i = 1 To n
If Cells(i, "F").Value = "red" Then
Range("A" & i & ":E" & i).Interior.ColorIndex = 3
Range("G" & i & ":M" & i).Interior.ColorIndex = 3
End If
Next
End Sub

 

[JE McGimpsey]

One way:

Put this in your worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rIntersect As Range
Dim rCell As Range
Dim nCI As Long
Set rIntersect = Intersect(Target, Columns("F"))
If Not rIntersect Is Nothing Then
For Each rCell In rIntersect
With rCell
If LCase(rCell.Text) = "red" Then
nCI = 3
Else
nCI = xlColorIndexNone
End If
Union(.Offset(0, -5).Resize(1, 5), _
.Offset(0, 1).Resize(1, 7)).Interior.ColorIndex = nCI
End With
Next rCell
End If
End Sub