Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and VBA
Hi,
I have a sheet with conditional formatting. If the cell contains a zero then it turns yellow and it works fine. I created a macro to add all white cells containing a number greater then zero. To do that I check the Interior.ColorIndex. The funny part is If the cell is yellow because of the conditional formatting(meaning that the value is zero) the VBA code with the Interior.ColorIndex sees it has a code 2(white cell instead of yellow)! Is this normal and how can I fix it? Thank's ahead. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and VBA
Bobby explained on 2/23/2012 :
Hi, I have a sheet with conditional formatting. If the cell contains a zero then it turns yellow and it works fine. I created a macro to add all white cells containing a number greater then zero. To do that I check the Interior.ColorIndex. The funny part is If the cell is yellow because of the conditional formatting(meaning that the value is zero) the VBA code with the Interior.ColorIndex sees it has a code 2(white cell instead of yellow)! Is this normal and how can I fix it? Thank's ahead. The cell color is always the default value unless changed by CF (ConditionalFormatting). Interior.ColorIndex will persist to return the default color regardless of what color CF is rendering. What I find a lot of folks do is to have both true/false colors in play. This can be problematic (as you've discovered) depending on what's happening and so I find it easier to NOT PLAY the color game. That means I set the cell shading to a default color and let CF handle changing it based on how the logic of my criteria applies. If you're trying to test if a cell is changed by CF then test for the criteria you used for CF conditions to apply. If there's no match then the cell *should* be the default color. No color is the normal default but you can manually change that as desired, leaving CF to make changes when condition criteria is TRUE.. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and VBA
On Feb 23, 6:44*pm, GS wrote:
Bobby explained on 2/23/2012 : Hi, I have a sheet with conditional formatting. If the cell contains a zero then it turns yellow and it works fine. I created a macro to add all white cells containing a number greater then zero. To do that I check the Interior.ColorIndex. The funny part is If the cell is yellow because of the conditional formatting(meaning that the value is zero) the VBA code with the Interior.ColorIndex sees it has a code 2(white cell instead of yellow)! Is this normal and how can I fix it? Thank's ahead. The cell color is always the default value unless changed by CF (ConditionalFormatting). Interior.ColorIndex will persist to return the default color regardless of what color CF is rendering. What I find a lot of folks do is to have both true/false colors in play. This can be problematic (as you've discovered) depending on what's happening and so I find it easier to NOT PLAY the color game. That means I set the cell shading to a default color and let CF handle changing it based on how the logic of my criteria applies. If you're trying to test if a cell is changed by CF then test for the criteria you used for CF conditions to apply. If there's no match then the cell *should* be the default color. No color is the normal default but you can manually change that as desired, leaving CF to make changes when condition criteria is TRUE.. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion Thank's Garry If I may I do use the color as a switch. If it's yellow it's paid, white is not paid. If I understand what you are saying I should change my approach right? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and VBA
That is normal.
Excel's Interior.Colorindex does not recognize CF colored backgrounds. See Chip Pearson's site for info and code to assist with CF coloring. http://www.cpearson.com/excel/CFColors.htm Gord On Thu, 23 Feb 2012 14:50:52 -0800 (PST), Bobby wrote: Hi, I have a sheet with conditional formatting. If the cell contains a zero then it turns yellow and it works fine. I created a macro to add all white cells containing a number greater then zero. To do that I check the Interior.ColorIndex. The funny part is If the cell is yellow because of the conditional formatting(meaning that the value is zero) the VBA code with the Interior.ColorIndex sees it has a code 2(white cell instead of yellow)! Is this normal and how can I fix it? Thank's ahead. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and VBA
Bobby wrote :
On Feb 23, 6:44*pm, GS wrote: Bobby explained on 2/23/2012 : Hi, I have a sheet with conditional formatting. If the cell contains a zero then it turns yellow and it works fine. I created a macro to add all white cells containing a number greater then zero. To do that I check the Interior.ColorIndex. The funny part is If the cell is yellow because of the conditional formatting(meaning that the value is zero) the VBA code with the Interior.ColorIndex sees it has a code 2(white cell instead of yellow)! Is this normal and how can I fix it? Thank's ahead. The cell color is always the default value unless changed by CF (ConditionalFormatting). Interior.ColorIndex will persist to return the default color regardless of what color CF is rendering. What I find a lot of folks do is to have both true/false colors in play. This can be problematic (as you've discovered) depending on what's happening and so I find it easier to NOT PLAY the color game. That means I set the cell shading to a default color and let CF handle changing it based on how the logic of my criteria applies. If you're trying to test if a cell is changed by CF then test for the criteria you used for CF conditions to apply. If there's no match then the cell *should* be the default color. No color is the normal default but you can manually change that as desired, leaving CF to make changes when condition criteria is TRUE.. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion Thank's Garry If I may I do use the color as a switch. If it's yellow it's paid, white is not paid. If I understand what you are saying I should change my approach right? Not necessarily! If 'not paid' is the default color and CF switches that when 'paid' then you have a good design. However, if you deliberately use some mechanism to color 'not paid' values then I suggest you change that. My thinking is to 'flag' unpaids with a color so they stand out, given that paid requires no action. Unpaid requires whatever it takes to get paid and so, IMO, those should be 'flagged' with color by CF.<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |