Conditional Formatting

[Guest]

I am trying to make a conditional formatting that is similar to the Conditional Formatting that is included in Excel. The only difference is that I need to have about 10 different conditions that I would like to test, and format.

The formatting is based on "Live Prices" in my worksheet that is updated continously. If the prices go below or above certain levels (Other predefined cells in the worksheet) I want the colour of the cells to change. The conditional Formatting in Excel would be perfect, but it is only 3 conditions to test, and I need about 10 different conditions to test. Anyone have any idea of how I could solve this problem?

 

[Gord Dibben]

Peter

You can use event code in your worksheet....

Example

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
rng.Font.ColorIndex = Num
Next rng
End Sub

Gord Dibben Excel MVP