Conditional Format

[LiAD]

Hi,

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

Thanks

 

[Mike H]

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

 

[David Biddulph]

Excel 2007, or VBA.

 

[LiAD]

Thanks,

Works fine for A1 but how do i extend the range? Sorry I know zero on VB.

Thanks

 

[LiAD]

Sorry should have said its 2003 and I need the range to go horizontal not
vertical.

Thanks

 

[LiAD]

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

 

[Mike H]

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

 

[LiAD]

Seems to work perfectly

thankyou

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

 

[Mike H]

glad i could help

Seems to work perfectly

thankyou