Excel 2010 Conditional Formatting 2

[SPYDERIX]

Hi there,

I have been tearing my hair out for 2 days trying to get this simple conditional to work. This only seems to work if I highlight one cell at a time and put in the same formula for each one but I need the formatting to apply to many cells all over the current worksheet.

I go into the conditional formatting (to manage rules), I add a new one, then set it to formula. I type in "=AC7>AF7" (WITHOUT QUOTES) and then set the background to be black and the font to be red and bold then try to select multiple cells but it doesn't seem to like me. It will format about 3 cells and apparently just give up. I have also tried to highlight all the cells I want formatted on the page then go into conditional and it's no dice again. I literally have to click on one cell, format it, click on the next, format with the exact same info (same formula, colors etc) and then it appears to co-operate.

What am I doing wrong? Why won't this work for me?

These are all the cells this needs to apply to:
=$D$7:$I$7,$D$9:$I$9,$D$11:$I$11,$D$13:$I$13,$D$15:$I$15,$D$17:$I$17,$L$15:$Q$15,$L$17:$Q$17,$T$15:$Y$15,$T$17:$Y$17,$AB$15:$AG$15,$AB$17:$AG$17,$T$7:$Y$7,$T$12:$Y$12,$D$24:$I$24,$L$24:$Q$24,$T$24:$Y$24,$AB$24:$AG$24,$D$31:$I$31,$L$31:$Q$31,$T$31:$Y$31,$AB$31:$AG$31,$D$38:$I$38,$L$38,$L$38,$L$38:$Q$38,$T$38:$Y$38,$AB$38:$AG$38

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

hi,

if the cells you want to CF are contiguous, select ALL the contiguous cells and then open the CF Window and apply the formula with reference to the top row and ALL the cell will inherit that CF.

if your cell or cell groupings are not contiguous, then CF the cell or cell group with the CF as outlined above and then use the format painter to propagate to other cell/cell groups.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYDERIX]

What do you mean: with reference to the top row?

What is the format painter?


What is really strange is that I'll highlight all the contiguous cells then do the CF window and out of 6 cells 2 random ones will highlight. Then if I change the cell data where the formula references I'll get only 1 more cell highlight from the conditional. Seems like something is majorly screwed here. This doesn't make any sense.

My formula in the CF is simply =AC7>AF7 Now AC7 happens to be a merged cell AC7 and AD7 and AF7 is a merged cell from AF7 and AG7. Would this cause this kind of problem?

I think I'm going to start a brand new .xlsx file and see if I can't get CF to work on a brand new workbook with no merged cells. Very simplistic and see if that works.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SPYDERIX]

Just tried this and still no dice on something very simplistic

File here:

http://www.nateryder.com/simple.xlsx

(NO VIRUSES)

Change A1 back and forth from 1 to 6 etc. Just make it higher and lower than B1 and watch what the CF does from A4:A10

If A1 is < B1 then the first 3 aren't colored but the last 4 are and when you make A1 > B1 the first is colored, the next 2 aren't, and the last 4 are.

Something is very strange here!!!

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SPYDERIX]

Here is a screenshot in case it's just my comp being lame

http://www.nateryder.com/simple.jpg

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

this is a better representation of your ranges..
[tt]
$D$7:$I$7 $T$7:$Y$7
$D$9:$I$9 $T$12:$Y$12
$D$11:$I$11
$D$13:$I$13
$D$15:$I$15 $L$15:$Q$15 $T$15:$Y$15 $AB$15:$AG$15
$D$17:$I$17 $L$17:$Q$17 $T$17:$Y$17 $AB$17:$AG$17
$D$24:$I$24 $L$24:$Q$24 $T$24:$Y$24 $AB$24:$AG$24
$D$31:$I$31 $L$31:$Q$31 $T$31:$Y$31 $AB$31:$AG$31
$D$38:$I$38 $L$38:$Q$38 $T$38:$Y$38 $AB$38:$AG$38
[/tt]
Let's start here.

For each of these groups, what CF LOGIC do you need to apply?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYDERIX]

It's the same formula for all.

Essentially it's =IF(AC7 > AF7, "CONDITIONAL", "NO CONDITIONAL")

Whatever way you write that in the formula section. I guess I was probably doing something wrong should have been more like =$AC$7>$AF$7

All the sections are to turn black background fill, red writing in bold. That's it

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

so are you saying that in ALL cells in the CF ranges refer to AC7 & AF7?

In that case your CF formula is
[tt]
=$AC7 > $AF7
[/tt]
if each row refers to columns AC & AF for each respective ROW

However if ALL cells in the CF ranges refer to $AC$7 & $AF$7 regardless of row, then your CF formula is
[tt]
=$AC$7 > $AF$7
[/tt]
No IF() formula!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYDERIX]

PERFECT!!!

Finally this works: =$AC$7 > $AF$7

I knew it wasn't the IF formula, I wrote that for you to understand more than anything what exactly I was after.

Now, can you please explain to me (other than the fact there is a dollar sign) what the difference is between:

=AC7 > $AF7
=$AC7 > $AF7
=$AC$7 > $AF$7

Also as one final question, is there a way to do conditional formatting based on this formula to change the TAB color. So if my formula is true change the TAB color to Red otherwise revert to the colors I have already choosen?

Thanks man!

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

[tt]
A1 column relative, row relative
$A1 column absolute, row relative
A$1 column relative, row absolute
$A$1 column absolute, row absolute
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYDERIX]

Ah cool. Thanks.

Do you know about the tab colors too?

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

AFAIK, there is no CF for TABS. That would have to be done using VBA.

If you wish to pursue a VBA solution, please post your question in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYDERIX]

Done. Thanks Skip!

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.