Conditional Formatting

[dcoledc]

I have a series of cells that I turn yellow when the user enters a 1 in the cell. When the 1 is deleted the conditional formatting is no long true so should then return the color to its original color. Or at least that is my understanding.

My formatting works fine in Excel 2003 and 2007, but in 2010, sometimes it doesn't work. And that "sometimes" is the problem. It works inconsistantly and b/c it is inconsistant, I don't really know how to address it.

Has anyone else come across this behavoir and how did you fix it?

The format rule I am using is:

Format only cells that contain

Cell value equal 1

 

[danzon]

I have come across this and it seems to be some kind of a screen refresh problem.

Enter your 1 ... cell turns Yellow...
now delete the 1... cell stays yellow.
scroll your page so that the cell in question moves out of screen, and then scroll back to it... the yelllow is now gone...

I don;t know what causes it or how to work around except to scroll up and down to move the cell in and out of view to force a refresh

 

[dcoledc]

Yes, I have noticed that as well. I'm wondering if VBA would solve instead of conditional formatting.

 

[dcoledc]

VBA worked great. Does exactly what I want it to do.

 

[T. Valko]

I have a series of cells that I turn yellow when the user enters a 1 in the cell. When the 1 is deleted the conditional formatting is no long true so should then return the color to its original color. Or at least that is my understanding.

My formatting works fine in Excel 2003 and 2007, but in 2010, sometimes it doesn't work. And that "sometimes" is the problem. It works inconsistantly and b/c it is inconsistant, I don't really know how to address it.

Has anyone else come across this behavoir and how did you fix it?

The format rule I am using is:

Format only cells that contain

Cell value equal 1

I've experienced this before in Excel 2002.

The "problem" only happened a couple of times so I took no corrective action.

I've also found this:

http://www.pptfaq.com/FAQ00129.htm

 

[T. Valko]

I have come across this and it seems to be some kind of a screen refresh problem.

Enter your 1 ... cell turns Yellow...
now delete the 1... cell stays yellow.
scroll your page so that the cell in question moves out of screen, and then scroll back to it... the yelllow is now gone...

I don;t know what causes it or how to work around except to scroll up and down to move the cell in and out of view to force a refresh

I've experienced this exact same thing in Excel 2002.

It only happened a couple of times so I never bothered trying to correct it.

See this:

http://www.pptfaq.com/FAQ00129.htm

 

[dcoledc]

Still bothered me so I kept searching and found what seems to be a solution. Place this code in your worksheet change mod:

 Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.ScreenUpdating = True
End Sub

Seems to work. Hope it stays that way.