Using cell color in conditional format makes Excel unstable

[Andrew]

I have a range of cells that contain data. Some cells are locked to prevent
the data being changed. Others are unlocked so that the user may enter
data. These unlocked cells are highlighted by setting the cell color to
yellow (ColorIndex=19).

Under certain circumstances the yellow cells may be locked. I wish to
highlight this by changing the cell color. To do this I use a conditional
format to set a different cell color.

The conditional format formula in cell B3 is:

=and(CellColor(B3)=19,Cell("Protect",B3)) 'ie if cell is yellow and
locked

The function CellColor() is

Function CellColor(ThisCell As Range) As Integer
CellColor = ThisCell.Interior.ColorIndex
End Function

This appears to make Excel totally unstable. Every time I now edit cell B3
Excel crashes with the message "Microsoft Office Excel has encountered a
problem and needs to close. We are sorry for the inconvenience."

PS I realise there is probably a different way of achieving the above, but
this is a very simplified version of a more complex design. I basically
need to know how to use the current cell color as part of the conditional
formatting without Excel crashing.

 

[Bob Phillips]

Aren't the two conditions testing the same thing. Why not just test for the
cell colour?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andrew]

Aren't the two conditions testing the same thing. Why not just test
for the cell colour?

Because some cells will be:
yellow and not locked
yellow and locked *
clear and locked

I want to identify the highlighted condition *

The problem still occurs if I simplify the problem to just using the cell
color. Perhaps a better (simpler) example is to make the text bold if the
color is yellow, normal otherwise.

The conditional format formula in cell B3 is:
=CellColor(B3)=19 'ie if cell is yellow

The conditional format applied if the condition is true is:
Bold

The function CellColor() is:
Function CellColor(ThisCell As Range) As Integer
CellColor = ThisCell.Interior.ColorIndex
End Function

As soon as you change the color of cell B3, Excel crashes. I am using Excel
2003 SP2.