Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting rule precedence difficulties
I have three conditional formatting rules (in this order) that apply to a
20x20 array 1) If the cell values are 0, change the font color to white (essentially "blanking" the cell) 2) If a certain cell value (say A2 - not in the array) equals 1 or 3, format the array values as currency. 3) If that same cell (A2) equals 2, format the array values as integers. The value of A2 changes based on which one of 3 radio buttons has been selected. When I select the first or the third radio button, the value in A2 changes to 1 or 3 respectively and Excel 2007 dutifully formats the values as currency. However, when I select the second radio button, the value in A2 changes to 2, but Excel does not format the array values as integers, leaving them as currency instead. None of the "Stop If True" boxes are checked for the three rules. If I switch the precedence of rule 2 and 3 (moving 3 up or 2 down), the opposite happens and the array values stay formatted as integers. Can someone tell me how I can get the formatting to properly change based on the value of A2? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting rule precedence difficulties
"Luke" wrote in message
... I have three conditional formatting rules (in this order) that apply to a 20x20 array 1) If the cell values are 0, change the font color to white (essentially "blanking" the cell) 2) If a certain cell value (say A2 - not in the array) equals 1 or 3, format the array values as currency. 3) If that same cell (A2) equals 2, format the array values as integers. The value of A2 changes based on which one of 3 radio buttons has been selected. When I select the first or the third radio button, the value in A2 changes to 1 or 3 respectively and Excel 2007 dutifully formats the values as currency. However, when I select the second radio button, the value in A2 changes to 2, but Excel does not format the array values as integers, leaving them as currency instead. None of the "Stop If True" boxes are checked for the three rules. If I switch the precedence of rule 2 and 3 (moving 3 up or 2 down), the opposite happens and the array values stay formatted as integers. Can someone tell me how I can get the formatting to properly change based on the value of A2? What are the actual formulas you are using for rules 2 & 3? V |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting rule precedence difficulties
I think you must change the order of priority by swapping 2 and 3.
Luke wrote: I have three conditional formatting rules (in this order) that apply to a 20x20 array 1) If the cell values are 0, change the font color to white (essentially "blanking" the cell) 2) If a certain cell value (say A2 - not in the array) equals 1 or 3, format the array values as currency. 3) If that same cell (A2) equals 2, format the array values as integers. The value of A2 changes based on which one of 3 radio buttons has been selected. When I select the first or the third radio button, the value in A2 changes to 1 or 3 respectively and Excel 2007 dutifully formats the values as currency. However, when I select the second radio button, the value in A2 changes to 2, but Excel does not format the array values as integers, leaving them as currency instead. None of the "Stop If True" boxes are checked for the three rules. If I switch the precedence of rule 2 and 3 (moving 3 up or 2 down), the opposite happens and the array values stay formatted as integers. Can someone tell me how I can get the formatting to properly change based on the value of A2? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting rule precedence difficulties
The actual formulas a
=OR($A$2=1,$A$2=3) - to format cells as currency =$A$2=2 - to format cells as integers Changing the priority of the two doesn't help. If I put the last condition in the second position, then all my values stay formatted as integers. For some reason, it is getting stuck on that second condition, even without any of the "Stop if true" boxes being checked. "Victor Delta" wrote: "Luke" wrote in message ... I have three conditional formatting rules (in this order) that apply to a 20x20 array 1) If the cell values are 0, change the font color to white (essentially "blanking" the cell) 2) If a certain cell value (say A2 - not in the array) equals 1 or 3, format the array values as currency. 3) If that same cell (A2) equals 2, format the array values as integers. The value of A2 changes based on which one of 3 radio buttons has been selected. When I select the first or the third radio button, the value in A2 changes to 1 or 3 respectively and Excel 2007 dutifully formats the values as currency. However, when I select the second radio button, the value in A2 changes to 2, but Excel does not format the array values as integers, leaving them as currency instead. None of the "Stop If True" boxes are checked for the three rules. If I switch the precedence of rule 2 and 3 (moving 3 up or 2 down), the opposite happens and the array values stay formatted as integers. Can someone tell me how I can get the formatting to properly change based on the value of A2? What are the actual formulas you are using for rules 2 & 3? V |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting rule precedence difficulties
"Luke" wrote in message
... The actual formulas a =OR($A$2=1,$A$2=3) - to format cells as currency =$A$2=2 - to format cells as integers Changing the priority of the two doesn't help. If I put the last condition in the second position, then all my values stay formatted as integers. For some reason, it is getting stuck on that second condition, even without any of the "Stop if true" boxes being checked. Strange! Things I would check: Presumably the number in A2 is only an integer 0,1,2,3,4? You could try putting the above formulas (with IFs) in blank cells and just checking that they do behave as they should. V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Icon Rule - Three Color Arrows | Excel Discussion (Misc queries) | |||
Expanding Conditional Format Rule to entire Column | Excel Discussion (Misc queries) | |||
Copying Conditional Formatting rule | Excel Worksheet Functions | |||
Copying a range adjacent to Conditional Format - CF Rule adds to P | Excel Discussion (Misc queries) | |||
Difficulties with date in conditional calculations | Excel Worksheet Functions |