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

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/

Why not use regular conditonal formatting with a formula similar to

=countif($B$2:$B$6, B7)=1
--
HTH...

Jim Thomlinson


"wpshop" wrote:

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