Using cell color in conditional format makes Excel unstable
"Andrew" wrote in message
...
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."
Bob Phillips wrote:
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.
|