Reluctant Conditional Formatting

[Cyberdude]

I have an Excel workbook with three sheets that have conditional formats in the header rows. If the following conditions are true, then the interior color turns red.

=AND(TRIM(K4)="Override",ErrMsgOvrrdeFlg=0)

Strangely, the conditional formatting doesn?t occur until I click on the cell and open, then close, the conditional formatting dialog box.

I have many other conditional formats on the same sheets, and they all work OK. I?ve been using these same conditional formats for literally years, and they have always worked OK, but within the last few months these have started misbehaving.

Does this strangeness sound familiar to anyone? Keep in mind that I don?t have to do anything but open and close the dialog box to make them work.

[Bob Phillips]

Manual calculation set?

[Cyberdude]

At this time it is hard to know what the Manual/Automatic condition state was. However, I have in my Workbook_Open and Workbook_BeforeClose logic that asks if Calculation is set to manual, and if it is, it changes it to Automatic, then executes a Msgbox that tells me about it so I can look for the reason. I kinda think it was not Manual, but it's something I have to wait a week for in order to tell. My conditions are VERY time oriented, and this situation only occurs on a Friday. I'll check it out though. I might say that once the conditional format executes, it is stable, i.e., it doesn't happen each time I open the workbook.

[JonPeltier]

At this time it is hard to know what the Manual/Automatic condition state was.

Why? Doesn't Tools menu > Options > Calculation tab tell you?

Als, what's ErrMsgOvrrdeFlg? Is it a non-volatile UDF? It might not be recalculated until you encounter it somewhere and force a recalc (like opening it in the CF dialog).

[Cyberdude]

OK, I verified that I do not have Calculation=xlManual.
ErrMsgOvrrdeFlg is just a boolean cell that I manually change when I don't want error messages etc. to display when I want to print the sheet. I use it about once a week,
and I have a button that tells me what it's current state is at all times. Wait a minute ... maybe that is the problem. I'm not sure why opening/closing the conditional formatting dialog box would change its effect, however. I'll follow up on that. Thanx for the suggestion.

[JonPeltier]

and I have a button that tells me what it's current state is at all times.

Why do you need a button? If you can see the cell, you can read the state. Too many bells and whistles complicate matters, make it harder to maintain your project, and may have unintended and hard to discover consequences.

[Cyberdude]

The button is what I use to toggle the message suppression on and off. The current state is written on the button and it also changes color from green to red. It's a quirk of mine...I like it that way. Thanks again.