Not enough conditional formatting

[alex.ferrazza]

Hi there,
I have created a calendar to help me in my work and I need to
highlight certain dates in different colour pending on if the cell
next to it has a value or not, I have devised 5 values but can only
have a maximum of 3 conditional formatting options.

W = Week end - back ground red - foreground black
B = Bank holiday - background light blue - foreground Black Bold
I = Inset day - Background light yellow - foreground Blue Bold
H = Holiday - Background light gray - foreground Black Bold
D = Half day - background light brown - foreground Blue Bold
X = Tradining day - no change from general settings

assuming that B8 has Value "B" then B8 and C8 background light blue
foreground Black Bold

is it possible?

 

[ryguy7272]

This question comes up every once in a while; look here:
http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1

Regards,
Ryan---

 

[Mike H]

you dont say whay the range is aprt from B8 so I guessed. Right click the
sheet tab view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
Select Case UCase(Target.Value)
Case "W"
icolor = 3
fcolor = 1
Case "B"
icolor = 8
fcolor = 1
Case "I"
icolor = 6
fcolor = 41
Case "H"
icolor = 15
fcolor = 1
Case "D"
icolor = 53
fcolor = 5
Case Else

End Select
With Target.Offset(0, 1)
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
End If
End Sub


Mike