Add more than 3 conditional formats


W

wpshop

I am trying to create a VBA code based on what I have found in similiar
questions and I am failing miserably. Basically I need my text in cells B7,
B8, B9, B10 and B11 to format as red/bold if any of the names listed in a
cell matches a name listed in cells B2, B3, B4, B5 or B6.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("B7:B11")) Is Nothing Then
Select Case Target
Case Is = "B2"
icolor = 6
Case Is = "B3"
icolor = 6
Case Is = "B4"
icolor = 6
Case Is = "B5"
icolor = 6
Case Is = "B6"
icolor = 6
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub
 
Ad

Advertisements

M

Mike Anas

Do you have to do it with VBA code, or are you assuming it can't be
done otherwise? Without VBA, just create the one conditional format
with the CELL VALUE IS EQUAL TO:

VLOOKUP(<currentCell>,B2:B6,1,false)

If the value of the current cell is found in the range, the selected
formatting will be applied.

Mike Anas
http://mikeanas.googlepages.com/
 
Ad

Advertisements

J

Jim Thomlinson

Why not use regular conditonal formatting with a formula similar to

=countif($B$2:$B$6, B7)>=1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top