More than 3 on Conditional Formatting

[Kardon Coupé]

Can it be done?

I don't want to have to write a routine in VBA to scan all the cells just to
change the colour.....

i.e. 1 is red 2 is a lighted red, 3 is amber 4 is a ligther amber, and 5
green and 6 a lighter green...

I need some help here guys...

Anyone point me in the right directions...

Regards.

 

[Don Guillett]

modify one of these. The first one belongs in the SHEET code module.
right click on the sheet tab>view code>insert this>save

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
With Target.Interior
Select Case UCase(Target)
Case "ASK"
..ColorIndex = 4
Case "BASK"
..ColorIndex = 6
Case "BID"
..ColorIndex = 8
Case "BBID"
..ColorIndex = 44
Case "TRD"
..ColorIndex = 38
Case Else
..ColorIndex = 0
End Select
End With
End Sub

This one can be anywhere and assigned to a button.

Sub colorcells()
For Each c In Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
With c.Interior
Select Case UCase(c)
Case "ASK"
..ColorIndex = 4
Case "BASK"
..ColorIndex = 6
Case "BID"
..ColorIndex = 8
Case "BBID"
..ColorIndex = 44
Case "TRD"
..ColorIndex = 38
Case Else
..ColorIndex = 0
End Select
End With
Next
End Sub

 

[Don Guillett]

Re-sending as I don't think it went thru the 1st time.

modify one of these. The first one belongs in the SHEET code module.
right click on the sheet tab>view code>insert this>save

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
With Target.Interior
Select Case UCase(Target)
Case "ASK"
..ColorIndex = 4
Case "BASK"
..ColorIndex = 6
Case "BID"
..ColorIndex = 8
Case "BBID"
..ColorIndex = 44
Case "TRD"
..ColorIndex = 38
Case Else
..ColorIndex = 0
End Select
End With
End Sub

This one can be anywhere and assigned to a button.

Sub colorcells()
For Each c In Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
With c.Interior
Select Case UCase(c)
Case "ASK"
..ColorIndex = 4
Case "BASK"
..ColorIndex = 6
Case "BID"
..ColorIndex = 8
Case "BBID"
..ColorIndex = 44
Case "TRD"
..ColorIndex = 38
Case Else
..ColorIndex = 0
End Select
End With
Next
End Sub