Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 : conditional format for 5 format settings
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(Risikoma trix;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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 : conditional format for 5 format settings
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) "Luc" wrote in message ... 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(Risikoma trix;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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format text Excel 2003 | Excel Discussion (Misc queries) | |||
Conditional Format Picture in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2007 Vs 2003 Conditional Format | Excel Discussion (Misc queries) | |||
Conditional format in excel 2003 based on another cell? | Excel Discussion (Misc queries) | |||
can't copy conditional format formulas in Excel 2003 | Excel Programming |