Mike,
There's really not another place to get the code to act like that without
some kind of code change - as you've figured out.
Excel does not trigger the change event when the evaluation of a formula
results in a change.
There are a couple of ways to tackle this. One is to continue to use the
_Change() event and have it watch for changes to cells that are used in the
formulas in the cells that you do want to change color for. When a change
happens in one of those, you look at the ones that may need a color change
and see if it needs to be done.
The second way (possibly much busier) is to use the _Calculate() event and
check the values each time that event triggers.

I think you will find this discussion right on target with what you are
trying to do. Read through it completely because it ends up being a
situation like you are describing: change in cell in one column causes other
changes in other cells, and it is the other cells that need the format change:
http://www.microsoft.com/office/comm...2-576b132090f2

Good luck.


"MikeA" wrote:

I have used this code to work on a problem that I am having getting more the
3 colors out of Excel 2000. I am a code novice so please bear with me. I have
modified this code and it works great if I am entering data directly to the
worksheet. However, in my case the data is being brought to this sheet from
another based on a formula and the colors do not change when I update the
other sheet. How do I need to modify this code to make that work? Or do I
need to use a different event? If yes, how would the coding for that event
look like?

thanks

--
MikeA


"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks