Conditional Format

  • Thread starter Thread starter LiAD
  • Start date Start date
L

LiAD

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks
 
Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike
 
Using 2003.

Solution wise I dont mind if it has to be VBA, just means I won't understand
a sausage. I can sort of understand the programs that others write but can't
even get close to doing one myself.

Thanks

"David Biddulph" wro
 
Hi,

This now works for row 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Rows(1)) Is Nothing Then
Select Case Target.Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 4
icolor = 15
Case Is = 5
icolor = 22
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
 
Seems to work perfectly

thankyou

Mike H said:
Hi,

This now works for row 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Rows(1)) Is Nothing Then
Select Case Target.Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 4
icolor = 15
Case Is = 5
icolor = 22
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
Click to expand...
 
Back
Top