Conditional formatting issues in XL 2010

[Sanmrxl]

While using conditional formatting in XL 2010, I am facing this problem.

( I am stating a simple similar scenario, which has the same issue occuring)

I wish to highlight a cell, if it is not a blank.

I have entered a simple conditional formula =$A13<>"" -- with a format color.

I now wish to copy and paste this format to each of the cells below.

On doing so ( Special Pasting only the format),
and on checking the conditional formula for the cell A14, it is correct - =$A14<>""

But for the cells A15 and A16 etc..I find that the formula does not change to =$A15<>"" or =$A16<>"" but is sticking to =$A14<>""

I have checked many different solutions advised, but nothing seems to work.
( And worst of all, some issues seem to point to this issue, but the solution given seems to work for them but not for me !!!)

I have also tried using the "Applies to" as suggested --BUT JUST NOTHING SEEMS TO WORK.

I realised that when I copy paste the first cell's conditional format to each cell individually,
then the formula gets copied correctly, but this is definitely NOT THE SOLUTION.

( I MISS XL 2003 so much-- WAS SO MUCH CLEAR BETTER AND DELIGHT TO USE !)

Hoping for relief at the earliest.

 

[Joe4]

Two options:

1. Use the Format Painter (looks like the Paintbrush - this was in 2003 also).

- or -

2. Highlight all the cells you want to at once (i.e. A13:A16), and the write the Conditional Formatting formula as it applies to only the first cell in your range (A13). The formulas for the other cells will adjust automatically, as long as you don't make A13 an absolute reference by putting a dollar sign ($) in front of the 13.

 

[Sanmrxl]

Hello Joe,
Thanks for your quick response.
What you have suggested, was tried by me again now ( I remember this suggestion was one of those in other inet nodes )
But the issue remains.
I am able to see the conditional formatting for each cell only with this formula
=A13<>"" for all the selected cells whereas I expect =A14<>"" and =A15<>"" etc.

Not sure what I am missing

 

[Joe4]

Which of my two suggestions did you try?

Exactly what cells are you trying to apply this to?

For your formula:

[COLOR=#333333]=A13<>""[/COLOR]

are you applying that to cell A13, or another cell?
What about all the others (does the cell address match the cell you are trying to apply the formula to)?

 

[Sanmrxl]

I tried your second suggestion.

To make my issue clearer, I am restating the problem as below, with a new smaller example.

In cells A1 to A4
I wish to color the cells which are not blanks.

So I select the range A1:A4.

I enter the conditional format for cell A1, as below;
=A1<>"" and give a background color (Say Green) to fill if the condition is true.

I would expect
cell A2 to have conditional formatting formula as =A2<>""
cell A3 to have conditional formatting formula as =A3<>""
cell A4 to have conditional formatting formula as =A4<>""

but this is not what is happening - I find

cell A2 has conditional formatting formula as =A1<>""
cell A3 has conditional formatting formula as =A1<>""
cell A4 has conditional formatting formula as =A1<>""


But just now I have a new find.
Though the format formula of the cell A2 shows as =A1<>""
it is formatting correctly !!!!!

That is to say,
it does NOT seem to be dependent on CELL A1, even though it is being displayed in the conditional formatting formula.

The Cells A2 is correctly colored green, when it is not blank and has no color when blank.

Now does that mean that the text displayed in the conditional formatting formula would be inconsistent, and we have to get used to it ?

I hope I have explained my issue clearly.

 

[Joe4]

When you are checking the Conditional Formatting for cell A2, what cell (or cells) do you have highlighted at that time? Just one cell or multiple cells?

 

[Jonmo1]

To the best of my knowledge, this behavior is by design.

The formula you see displayed in the conditional formatting rules manager window is shown 'relative to' the top/left cell of the "Applies To" Range.
No matter whcih cell you have selected at the time.

But you can rest assured, each cell within that "Applies To" range is using the correct formula,
With the row and column references adjusted accordingly by the rules you applied with the absolute referencing in the formula $.

 

[Joe4]

The formula you see displayed in the conditional formatting rules manager window is shown 'relative to' the top/left cell of the "Applies To" Range.
No matter whcih cell you have selected at the time.

But you can rest assured, each cell within that "Applies To" range is using the correct formula,
With the row and column references adjusted accordingly by the rules you applied with the absolute referencing in the formula $.

OK, now I see what you are talking about (thanks for the explanation Jon!). Yes the screen just before the Formula shows you what range it applies to.

 

[Jonmo1]

Now does that mean that the text displayed in the conditional formatting formula would be inconsistent

I would say the opposite, It's now VERY consistent indeed.
The formula you see is always shown relative to the top left cell of the applies to range.
So when you're looking at your rules, take the top left cell of the applies to range, and the formula is shown as if that was the current active cell.

Although this part is true

and we have to get used to it ?

It is a big difference between how CF was done in XL2003.
But it's different..Better.. I think.

 

[Sanmrxl]

I am still not getting one point.

Pls note. I am not using "$" in my conditional formatting formula.

As I see the conditional formatting formula in CELL A1 as =A1<>"" ( in the conditional formatting manager),
I would expect to see the conditional formatting formula for say CELL A4 as =A4<>"",
which is not happening and I continue to see only =A1<>"".

In my opinion this is not a true representation of the conditional formatting formula.

Do you agree ?