Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2nd Conditional Formatting?
I want to set more than 3 conditional formats where the background color changes in one cell based on values found in two other columns within the worksheet. I need to setup at least 4-5 conditional formats. The actual conditonal formatting used now in the spreadsheet is as follows. So if there is a value of 1 thru 6 in column I or a valued of Green, Amber, Red, or Blue in column D, then I want to format the background color in a Monthly column of 1-6 as that particular color. I'm at a loss as to where to begin.
=(AND($I5=1,$D5="Green")) =(AND($I5=1,$D5="Amber")) =(AND($I5=1,$D5="Red")) =(AND($I6=2,$D6="Green")) =(AND($I6=2,$D6="Red")) =(AND($I6=2,$D6="Amber")) =(AND($I24=3,$D24="Green")) =(AND($I24=3,$D24="Red")) =(AND($I24=3,$D24="Amber")) I know that I could create a select case statement to color in the background, but I'm not sure how to code this to indicate when/where that should be done. Case "Red" oCell.Interior.ColorIndex = 3 oCell.Font.ColorIndex = 1 oCell.Font.Bold = True Any hints that you can give to get stated would be much appreciated? Worth at least 10 stars in my book. Thanks and have a great day. RonS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2nd Conditional Formatting?
depends on when you want to trigger the code. If the change to a cell will
only be done manually, then you can use the worksheet_change event. (right click on the sheet tab and select view code. in the top of the resulting module select worksheet from the left and Change from the right - not SelectionChange). -- Regards, Tom Ogilvy "RonS" wrote in message ... I want to set more than 3 conditional formats where the background color changes in one cell based on values found in two other columns within the worksheet. I need to setup at least 4-5 conditional formats. The actual conditonal formatting used now in the spreadsheet is as follows. So if there is a value of 1 thru 6 in column I or a valued of Green, Amber, Red, or Blue in column D, then I want to format the background color in a Monthly column of 1-6 as that particular color. I'm at a loss as to where to begin. =(AND($I5=1,$D5="Green")) =(AND($I5=1,$D5="Amber")) =(AND($I5=1,$D5="Red")) =(AND($I6=2,$D6="Green")) =(AND($I6=2,$D6="Red")) =(AND($I6=2,$D6="Amber")) =(AND($I24=3,$D24="Green")) =(AND($I24=3,$D24="Red")) =(AND($I24=3,$D24="Amber")) I know that I could create a select case statement to color in the background, but I'm not sure how to code this to indicate when/where that should be done. Case "Red" oCell.Interior.ColorIndex = 3 oCell.Font.ColorIndex = 1 oCell.Font.Bold = True Any hints that you can give to get stated would be much appreciated? Worth at least 10 stars in my book. Thanks and have a great day. RonS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2nd Conditional Formatting?
See my reply to your first question.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "RonS" wrote in message ... I want to set more than 3 conditional formats where the background color changes in one cell based on values found in two other columns within the worksheet. I need to setup at least 4-5 conditional formats. The actual conditonal formatting used now in the spreadsheet is as follows. So if there is a value of 1 thru 6 in column I or a valued of Green, Amber, Red, or Blue in column D, then I want to format the background color in a Monthly column of 1-6 as that particular color. I'm at a loss as to where to begin. =(AND($I5=1,$D5="Green")) =(AND($I5=1,$D5="Amber")) =(AND($I5=1,$D5="Red")) =(AND($I6=2,$D6="Green")) =(AND($I6=2,$D6="Red")) =(AND($I6=2,$D6="Amber")) =(AND($I24=3,$D24="Green")) =(AND($I24=3,$D24="Red")) =(AND($I24=3,$D24="Amber")) I know that I could create a select case statement to color in the background, but I'm not sure how to code this to indicate when/where that should be done. Case "Red" oCell.Interior.ColorIndex = 3 oCell.Font.ColorIndex = 1 oCell.Font.Bold = True Any hints that you can give to get stated would be much appreciated? Worth at least 10 stars in my book. Thanks and have a great day. RonS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |