Conditional Formats

  • Thread starter Thread starter Guest
  • Start date Start date
You will need to use VBA - sample below (courtesy of Bob Phillips).

Right-click on sheet, tab "View code" and copy / paste code below


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to your requirements


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub
 
Rob,

Right click the sheet tab, view code and paste this in. Change the ranges,
conditions and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

Mike
 
Back
Top