Conditional Formatting and VBA

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

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.
 
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.
Click to expand...

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
 
Bobby explained on 2/23/2012 :


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
Click to expand...

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?
 
Bobby wrote :
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?
Click to expand...

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
 
Back
Top