A
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.
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.