Need to make conditional format irreversible

[Jeremy]

So I'm having a bit of a problem with some conditional formatting (I posted
this question at the bottom of one I answered a couple of days ago and I
don't think anyone has seen it there).

I have a column that has cells that say "Incomplete" (with
red formating) and change to "complete" (green formating) when a value I look
up in another cell is met, say if a cell is 0 somewhere else, it's
incomplete, and if it's 1, it's complete. Once it turns to complete, I want
it to stay that way (until
next time I load the workbook) even if the cell I'm looking at turns back to
0. Any ideas? I tried a counter, nested ifs, maybe I need to do it in the
vba code?

Thanks.

 

[Shane Devenshire]

The whole idea of condtional formatting is that its conditional! If you
want to color a cell, say A1 red or green depending on another cell say B1
which contains 0 or 1, and you want the color to be retained when the value
changes again you are not asking for conditional formatting. You should do
this either by manually format the cell or write a vba program to do it for
you.

You don't have a condition to change the color back once it has changed, so
its all manual.

 

[Jeremy]

Ok, I can see that. I've been playing with the code and trying to use cases,
but I don't think that's the right direction.

Should I just write and If statement in VBA that looks for "complete" and
then ends and that's it?

Thanks. little more help would be great.