Conditional Formatting

[scareycarey]

Hi
I wonder if anyone can help

I have a spreadsheet and every cell has a conditional format in it (1230 cells)

The conditional format is a formula =$AD$9>$AA$173

All I want to do is take a $ out ( =$AD9>$AA$173) of each cell, is there an easy way to change all 1230 cells at once.

Thank you

Richard

 

[Tom Urtis]

If the formula is the same for each cell's conditional formatting formula, can you not select that range and edit its conditional formatting formula?

 

[Joe4]

Easiest way is to remove the Conditional Formatting, then reapply it.
If you highlight your entire range, and then write the formula as it applies to the first cell in the range only, Excel will make all the correct adjustments, assuming you have your relative/absolute referencing correct in your formula!

 

[scareycarey]

If I can Explain a bit more cos what you said is not working

Cell A9 =$F$9>$C$173 Cell G9 =$J$9>$G$173
Cell A13 =$F$13>$C$173 Cell G13 =$J$13>$G$173
Cell A17 =$F$17>$C$173 Cell G17 =$J$17>$G$173

What I want to achieve is to take the $ out Example ( =$AD9>$AA$173) of each row and column simultaneously keeping the references

Hope this is a better explanation

Richard

 

[Joe4]

We know. You can either Edit the conditions to do that (like Tom recommended), or remove and re-apply the formatting rules (like I recommended).

Did you try either one?

 

[scareycarey]

I have tried both ways and even though I have selected the current selection or the current worksheet edited or cleared and redone it only changes the one cell not all.

I must be doing something wrong but don't know what

 

[Tom Urtis]

"I must be doing something wrong but don't know what"

Agreed.

Try copying the one cell whose conditional formatting you edited. Then select the range of other cells that should have that same conditional formatting applied. Paste Special for Formats. True, that will also copy borders, fonts, and whatever other non-Conditional Formatting the copied cell may contain, but at least you can know if something totally bizarre is going on with your Conditional Formatting, which is unlikely.