C
Christopher Hogan
A lot has changed with Conditional Formatting between Excel 2003 and 2007. I
have a worksheet with three columns of data:
Location, Physical Inventory, Database Inventory
Let's say I have 25 rows of data.
I am using conditional formatting to test if A3 (Location) is blank. If it
is not, I want the cell's top border turned on. For this, I use "Formula Is"
=NOT(ISBLANK($A3)). In 2003, I would then copy cell A3 and use Paste
Special>Format to copy the format to A4:C25. If I then selected cell C25 and
looked at the conditional formatting, I would see "Formula Is"
=NOT(ISBLANK($A25)). This leaves a nice divider between locations. Location
only has one entry per location. It's otherwise blank.
I tried this same method in 2007 and ended up with a mess of overlapping
conditional formats. It copied the format to the first cell in the range and
then adjusted the range of the rule to encompass the selection. Now cell C25
shows Formula: =NOT(ISBLANK($A4)) Applies to =$A$4:$C$25. It seems that 2007
supports per cell rules, but prefers a single rule and a range. Starting
over, I created the rule in cell A3 "Formula: =NOT(ISBLANK($A3))". I then
edited the rule to give it the range "=$A$3:$C$25". This worked as I wanted.
It is confusing to select cell C25 and see the rule "Formula:
=NOT(ISBLANK($A3))" when in fact it is looking at cell A25, not cell A3.
However, it worked.
Next I wanted to compare column C to B. If they are not equal, I wanted the
cell in C to turn red. The new method worked here too. In C3, I used Cell
Value <> $B3 with a range of $C$3:$C$25.
Then, I needed to save it as a 2003 XLS file. I received an error about
overlapping ranges and having the "Stop If True" unchecked. My assumption was
that both rules would be saved and whichever was true first would apply. I
was wrong. I opened the file in 2003 and the second rule was gone. At that
point I gave up and redid the conditional formatting in 2003.
I just want to make sure I have what is going on straight in my head. When
saving as 2003 XLS, 2007 saves the first rule it encounters and deletes any
overlapping rules. Even if the rule would be valid in 2003. In 2007, you can
no longer copy and paste conditional formats. Rather, you define a rule and
give it a range. The conditional format rule shown in any specific cell
doesn't really show what is happening in that cell. Rather, it shows what is
happening in the first cell in the range, adjusted to the rest of the range.
So in cell C25, it will tell you it's looking at A3 when it is really looking
at A25.
Do I have that about right?
Thanks,
Christopher Hogan
MOS Master Certified
have a worksheet with three columns of data:
Location, Physical Inventory, Database Inventory
Let's say I have 25 rows of data.
I am using conditional formatting to test if A3 (Location) is blank. If it
is not, I want the cell's top border turned on. For this, I use "Formula Is"
=NOT(ISBLANK($A3)). In 2003, I would then copy cell A3 and use Paste
Special>Format to copy the format to A4:C25. If I then selected cell C25 and
looked at the conditional formatting, I would see "Formula Is"
=NOT(ISBLANK($A25)). This leaves a nice divider between locations. Location
only has one entry per location. It's otherwise blank.
I tried this same method in 2007 and ended up with a mess of overlapping
conditional formats. It copied the format to the first cell in the range and
then adjusted the range of the rule to encompass the selection. Now cell C25
shows Formula: =NOT(ISBLANK($A4)) Applies to =$A$4:$C$25. It seems that 2007
supports per cell rules, but prefers a single rule and a range. Starting
over, I created the rule in cell A3 "Formula: =NOT(ISBLANK($A3))". I then
edited the rule to give it the range "=$A$3:$C$25". This worked as I wanted.
It is confusing to select cell C25 and see the rule "Formula:
=NOT(ISBLANK($A3))" when in fact it is looking at cell A25, not cell A3.
However, it worked.
Next I wanted to compare column C to B. If they are not equal, I wanted the
cell in C to turn red. The new method worked here too. In C3, I used Cell
Value <> $B3 with a range of $C$3:$C$25.
Then, I needed to save it as a 2003 XLS file. I received an error about
overlapping ranges and having the "Stop If True" unchecked. My assumption was
that both rules would be saved and whichever was true first would apply. I
was wrong. I opened the file in 2003 and the second rule was gone. At that
point I gave up and redid the conditional formatting in 2003.
I just want to make sure I have what is going on straight in my head. When
saving as 2003 XLS, 2007 saves the first rule it encounters and deletes any
overlapping rules. Even if the rule would be valid in 2003. In 2007, you can
no longer copy and paste conditional formats. Rather, you define a rule and
give it a range. The conditional format rule shown in any specific cell
doesn't really show what is happening in that cell. Rather, it shows what is
happening in the first cell in the range, adjusted to the rest of the range.
So in cell C25, it will tell you it's looking at A3 when it is really looking
at A25.
Do I have that about right?
Thanks,
Christopher Hogan
MOS Master Certified