2nd Conditional Formatting?

[Guest]

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

 

[Tom Ogilvy]

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

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.

 

[Bob Phillips]

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)

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.