Format Cell Background on Condition

[Guest]

Can anyone help me with a VBA routine that will find all cells in a worksheet
that contain the string 'Member Totals' and then shade that cell and the five
adjacent cells (to the left) in grey.

I would use conditional formatting, but I have lots of merged cells so I
can't fill the formatting down.

 

[JE McGimpsey]

Yet another example of why merged cells should be outlawed...

 

[Tom Ogilvy]

Sub ColorCells()
Dim c As String
Dim rng As Range
Dim sAddr As String
c = "Member Totals"
Cells.Interior.ColorIndex = xlNone
Set rng = Cells.Find(what:=c, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
Set rng1 = rng.MergeArea(1)
Set rng1 = rng1.Offset(0, -5).Resize(1, 6)
rng1.Interior.ColorIndex = 15
Set rng = Cells.FindNext(rng)
Loop Until rng.Address = sAddr
End If
End Sub