format row on cell value loop

[Will]

Any ideas on a loop to loop through the cells in a row, then if any cell
in that row contains a specified value, then format that row containing
the value.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Frank Kabel]

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

 

[Ron de Bruin]

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