Not enough conditional formatting

  • Thread starter Thread starter alex.ferrazza
  • Start date Start date
A

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?
 
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
 
Back
Top