Format Cell Background on Condition

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.

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

In article <[email protected]>,
"PCullen" <[email protected]> wrote:

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

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

--
Regards,
Tom Ogilvy

"PCullen" <[email protected]> wrote in message
news:[email protected]...
> 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.