Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Using cell color in conditional format makes Excel unstable

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.


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default Using cell color in conditional format makes Excel unstable

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

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.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I override conditional format to modify color of 1 cell? kevpandrews Excel Discussion (Misc queries) 4 May 8th 23 11:45 AM
Excel 2002: Unstable color setting in converted file Mr. Low Excel Discussion (Misc queries) 1 July 10th 08 12:27 PM
Conditional format a cell based on color of another Marlo Excel Discussion (Misc queries) 3 January 21st 08 02:50 AM
Conditional format: change row color based on cell value Carelle Excel Discussion (Misc queries) 2 January 3rd 08 10:26 PM
Conditional Format? - color if cell is modified by user jhs626 Excel Programming 2 August 5th 05 03:03 PM


All times are GMT +1. The time now is 09:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"