change background color based on range value

[DanL]

I would like to search a spread sheet for a specific range value Ie

c1 c2 c3 c4 c5 c6
20 30 40
20 30 40

When 20 30 and 40 are found in neighboring columns I would like to turn the
background of these 3 cells blue. How would I code this?

Thanks in advance - Dan

 

[Paul]

Use conditional formatting over the first range.

Set condition 1 to Formula-is option and in next box add
=COUNTIF($D$1:$F$5,A1)>0
Format as appropriate.

$D$1:$F$5 is range being tested. Change as appropriate.
A1 is the first cell of the range being conditionally formatted.

There are lots of Youtube vids which may prove informative if you
want to learn more. Type conditional formatting in the site search
box to find vids on this particular aspect of Excel.

Regards
Paul

 

[Don Guillett]

Sub findgroups()
'With Worksheets(1).Range("a1:z500")
Set c = Cells.Find(20, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(, 1) = 30 And c.Offset(, 2) = 40 Then
c.Resize(, 3).Interior.ColorIndex = 33
End If
Set c = Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
'End With
End Sub