Excel 2003 : conditional format for 5 format settings

[Luc]

I have Excel 2003 : in this version you are limited to 3 conditional formattings,but i want to use 5 formats, so i run out of luck !

I want to use conditional format for the entire columm Q.

The text wich appears in the cells in column Q (Very Low, Low, Medium, High, Very high) is dependent of the Columns O & P.
The following formula is used :
=INDEX(Risikomatrix;VERGELIJKEN(O77;INDEX(Risikomatrix;0;1);0);VERGELIJKEN(P77;INDEX(Risikomatrix;1;0);0))

So the cells in column Q can have the value : Very Low, Low, Medium, High, Very high (wich is the result of the values of the cells in col O & P)

When the text in the cell is "Very low" => the color of the cell has to be Green
When the text in the cell is "Low" => the color of the cell has to be Yellow
When the text in the cell is "Medium" => the color of the cell has to be Orange
When the text in the cell is "High" => the color of the cell has to be Light red
When the text in the cell is "Very High=> the color of the cell has to be Dark red

Can someone help me with the VBA code.

Thanks,

Luc

 

[Rick Rothstein]

I'm guessing from what you wrote that Column Q contains formulas that display the text you posted when some other cells change their value. If that is correct, then right click the tab at the bottom of the worksheet you want this functionality on, select View Code from the popup menu that appears and then copy/paste the following into the code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
With Target.Dependents
If .Column = 17 Then ' 17 is Column "Q"
Select Case UCase(.Value)
Case "VERY LOW"
.Interior.ColorIndex = 4
Case "LOW"
.Interior.ColorIndex = 6
Case "MEDIUM"
.Interior.ColorIndex = 45
Case "HIGH"
.Interior.ColorIndex = 22
Case "VERY HIGH"
.Interior.ColorIndex = 3
Case Else
.Interior.ColorIndex = 0
End Select
End If
End With
End Sub

Now, when you change any cells that the formulas in Column Q depend on, then the affected cells in Column Q will change color as requested.

--
Rick (MVP - Excel)


I have Excel 2003 : in this version you are limited to 3 conditional formattings,but i want to use 5 formats, so i run out of luck !

I want to use conditional format for the entire columm Q.

The text wich appears in the cells in column Q (Very Low, Low, Medium, High, Very high) is dependent of the Columns O & P.
The following formula is used :
=INDEX(Risikomatrix;VERGELIJKEN(O77;INDEX(Risikomatrix;0;1);0);VERGELIJKEN(P77;INDEX(Risikomatrix;1;0);0))

So the cells in column Q can have the value : Very Low, Low, Medium, High, Very high (wich is the result of the values of the cells in col O & P)

When the text in the cell is "Very low" => the color of the cell has to be Green
When the text in the cell is "Low" => the color of the cell has to be Yellow
When the text in the cell is "Medium" => the color of the cell has to be Orange
When the text in the cell is "High" => the color of the cell has to be Light red
When the text in the cell is "Very High=> the color of the cell has to be Dark red

Can someone help me with the VBA code.

Thanks,

Luc