format row on cell value loop

  • Thread starter Thread starter Will
  • Start date Start date
Hi
have you consideres using a conditional format ('Format - conditional
format'). This could do the same.

If this is not sufficient for you please post back
 
Hi Will

For a VBA solution

This will look in the A column for "ron" and make the rows red
you can fill in more things in the Array if you want

Sub Color_rows()
Dim FirstAddress As String
Dim myArr As Variant
Dim rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("ron")
'You can also use more values in the Array
Cells.Interior.ColorIndex = xlNone
'set the fill color to "no fill" in all cells

With Range("A:A")
For I = LBound(myArr) To UBound(myArr)
Set rng = .Find(What:=myArr(I), After:=Range("A" & Rows.Count),
LookAt:=xlWhole)
'If you want to search in a part of the rng.value then use
xlPart
If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
rng.EntireRow.Interior.ColorIndex = 3
'make the row red
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <>
FirstAddress
End If
Next I
End With
Application.ScreenUpdating = True
End Sub
 
Back
Top