Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating/Excel 2007 to 2003
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 SpecialFormat 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating/Excel 2007 to 2003
I don't think the behaviour has changed... The conditon is shown as you
described but upto the first three conditions should be available in Excel 2003... I never had this problem. Unfortunately I do not have access to it now. Of course Excel 2003 stops after the first match.... "Christopher Hogan" wrote: 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 SpecialFormat 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating Using Office 2007 | Excel Worksheet Functions | |||
Excel 2007 conditional formating using 3 traffic lights on percent | Excel Discussion (Misc queries) | |||
2007 Excel conditional formating, reference cell containing specif | Excel Discussion (Misc queries) | |||
Yet another conditional formating question (Excel 2003) | Excel Discussion (Misc queries) | |||
Conditional Formating in Excel 2007 | Excel Worksheet Functions |