conditional format based upon fill in another cell (created by conditional format)

I have an xls that has conditional formating.

I was wondering if I can have conditional formating based upon the fill color of another cell that has fill based upon conditional formating.

Thanks

Bruce

P.S. see attached screen shot.

Sure. Click on one of the conditionally formatted cells > Conditional Formatting > Manage Rules > Change the "Applies to" area to include the column to the left of the $ figures.

I tried changing the "applied to" to include the date columns but then the date column gets filled and the $ column doesn't get filled.

current: =$D$3,$J$3,$J$17,$D$17
changed: =$C$3,$D$3,$I$3,$J$3,$C$17,$D$17,$I$17,$J$17

I'd the the conditional formating to only be based upon the column with the highest $ amount and then the date column to be filled if the $ column is filled.

Suggestions?

You can attach the actual Excel workbook that the screenshot from post #1 is coming from and we can get it sorted out for you.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

here it is:

thx

I'm going to start from scratch here. Conditional Formatting > Clear Rules > Entire sheet (this will clear all of the CF in this one worksheet).

Yellow CF:

Select cells C2:D13 > Conditional Formatting > New Rule > Use a formula

=MAX($D$2:$D$13)=$D2

Format: Fill yellow > OK > OK

Now repeat these steps for the other 3 boxes adjusting the selection range and CF formulas accordingly.


Green CF:

Select D14, D28, J14, and J28 (hold the Ctrl key to make multiple selections) > Conditional Formatting > New Rule > Use a formula

=MAX($D$14,$D$28,$J$14,$J$28)=D14

Format: Fill Green > OK > OK

See attachment for clarification.

can I assume the new formulas would look like this?

=MAX($D$16:$D$27)=$D16
=MAX($J$2:$J$13)=$J2
=MAX($J$16:$J$27)=$J16

If this is correct, each box is filling the highest month but I'm trying to fill the highest "january" month of each box.

I even tried using =MAX($D$2,$J$2,$D$16,$J$16)=$D2 but it didn't fill anything.

Suggestions?




Bruce

Ooh I see. I thought that each box was supposed to highlight its' own max. In that case, we can edit the formulas from post #6 to these:

C2:D13
=AND(MAX($D2,$D16,$J2,$J16)=$D2,$D2<>0)

I2:J13
=AND(MAX($D2,$D16,$J2,$J16)=$J2,$J2<>0)

C16:D27
=AND(MAX($D2,$D16,$J2,$J16)=$D16,$D16<>0)

I16:J27
=AND(MAX($D2,$D16,$J2,$J16)=$J16,$J16<>0)

The green CF formula from post #6 is still correct.

See updated attachment.

It's perfect....exactly what I was trying to do.

I'll just need to study the formula to get a better understanding on what it actually does for future formulas.

Thanks so much!

Bruce

Great! Happy to help.

Thanks for the rep!

kevinu, I assume you are referring to the formula from post #6.

We put the absolute sign ($) before the column letter since we do not want the column reference to change as the CF formula is "dragged over and down".
Note that we would not have to put any absolute signs in front of the column references if the CF formula was only applied to a single column.

A good way to envision how this works is to put the formula into another cell and drag it across and down to see what returns TRUE and what returns FALSE.

Everything that returns true will be conditionally formatted.

In the simplified example below (see screenshot), the TRUE's and FALSE's correspond to the values that are in the range B3:C5.

If you wanted to apply Conditional Formatting to highlight the highest number and it's corresponding letter, you would select B3:C5 and apply the formula that is shown in H3.

oh ok i see now thanks for the explination