Conditional Formatting not working on all selected cells?

[Zil]

I have a rather large excel 2010 sheet that needs conditional formatting, and the conditional formatting is only working for the first cell in the group of cells that have the rules applied to them. Four cells at a time have the same rules apply to them. The following are the steps I followed along with what happened:

1. Selected four cells.
2. Added 3 rules under conditional formatting>manage rules.
3. Clicked OK.
4. The font in the cells is supposed to be certain colors depending on the values, and if within normal spec remain the default font color.
5. The first cell in the group of four follows the rules.
6. The font in the last 3 cells turns orange, the color for the last rule, regardless of the value.


I have doubled check everything, and had a co-worker who I would rate excellent in her excel knowledge look at this. We are both stumped. I really don't want to have to go through 240 cells and individually add conditional formatting, so any help would be very much appreciated.

[Sisyphus]

Hi,
Did you, by any chance, include absolute referencing in your formatting conditions?
Formula is =A1=0 pr Formula is =A$1=0 will produce different results, the latter the kind of result you are describing.

[Zil]

Thanks for responding, but wheather or not the formula had absolute referencing did not a difference. Orginally it had the absolute reference, so I tried taking it out "just to see."

[pecoflyer]

Maybe post a small sample sheet ( not picture please) showing your problem would help

[Sisyphus]

There are two ways to set the conditional formatting with relative referencing.

1. Set one cell and copy the format down from there.
2. Select all cells to be formatted and enter the condition for the active (first) cell.

You used the second but you used absolute formatting and got an unexpected result. Note that you can't undo this kind of error by changing the reference style in one cell and "apply to all others". You have to delete the existing and create new. However, if the second way doesn't work, try the first.