Conditional formatting copying down different in 2007?

[ErikJan]

In Excel I can set conditional formatting in a cell to e.g. add a color if a formula is true like: =AND(V1<>W1,V1<>NA())

Then I double-click on the lower right cell dot to copy cell formulas and conf formatting down in the range. As the cond format is not absolute the formulas adapt to each cell e.g. for row 123: =AND(V123<>W123,V123<>NA())

I've always done it like this... in Excel 2007 however I cannot get this to work; no matter how I set the formula it doesn't adjust when I copy down in the range. What am I missing?

[HansV]

I just tried it in Excel 2007 SP2 and double-clicking the fill handle or dragging the fill handle extended the conditional formatting formula to the entire range. Looking at the rule, you'll still see the original formula (and the range it applies to), but it will be adjusted correctly.

[ErikJan]

Well... I'm still lost... I created the cond formula in W11 then copied it down. I'm showing the cond formula that I get when I selected cell W993.

OK, I can believe the formula behind the screen is still working but why is the cell not highlighted then? (the left column is V the right is W)... [W isn't NA() and is different from V]

[HansV]

The dialog you should look at is the Manage Rules dialog:

x83.png

As you can see, even if it is not entirely visible, the formula is the one for the cell in row 1, although a cell in row 5 is selected. The dialog clearly indicates that the formula applies to $C$1:$C$8.
If you click Edit Rule, you'll see the formula for cell C1 again, but this time it's not evident that it's the formula used for $C$1:$C$8. I admit that this is confusing.

[ErikJan]

Yeah, I got that far but why is my cond format not coloring my cell then???

[StuartR]

Yeah, I got that far but why is my cond format not coloring my cell then???

Does your formula work in any cell?

I don't think that =A1<>NA() is correct, I suspect you meant something like =NOT(ISNA(A1))

[ErikJan]

Yep, that was it... so simple... thanks to both for helping!

[Jan Karel Pieterse]

The CF manager in 2007 is a very bad piece of userinterface if you ask me. Try changing a conditional formatting rule which has been applied to multiple cells, for just one cell. Very, very difficult.

Also, be aware that 2007 has a known bug. If you copy a cell with CF on top of another cell with CF, you end up with BOTH sets of CF in the target cell. The original CF does NOT get replaced with the new CF.

[Rudi]

Wow..thanks for that tip. Thats NEW!

[ErikJan]

The CF manager in 2007 is a very bad piece of userinterface if you ask me. Try changing a conditional formatting rule which has been applied to multiple cells, for just one cell. Very, very difficult.

Also, be aware that 2007 has a known bug. If you copy a cell with CF on top of another cell with CF, you end up with BOTH sets of CF in the target cell. The original CF does NOT get replaced with the new CF.

Guess that with Office 2010 coming there's no chance this will be fixed in a -say- Office2007 SP3, right? Is the interface improved in O2010 and the bug removed as far as you can tell?

(more generic... as a new user of 2007, via the MS Homeuser program -I work for a company who has >30000 licenses worldwide- I might be able to get O2010 for maybe 15 euro's [legally that is], would it be worth my while to go to 2010? What would that bring me extra compared with 2007?)

[Jan Karel Pieterse]

The bug has been fixed. Haven't checked the UI yet.
I'd go for the upgrade, quite some interesting new features and bugfixes. And for only 15 Euro....