Conditional Formatting not automatically updating

[netrixuser]

Hello,
Apologies first as I "hijacked" a similar thread that was posted yesterday, my bad.

I have an issue where conditional formatting is not working automatically but rather needs some kind of "nudge" to work.
To explain - Cells C1 to C100 have a CF referencing cells A1 to A100. When the correct "trigger" is entered in, for example, cell A1 the CF in C1 will only update if:

I resize the row height or column width of the cell.​

I click to another tab in the workbook and click back again.​

I minimise Excel and maximise again.​

I edit C1 (using F2) and press enter​

All of the above will trigger the CF to work correctly.

Also, if I copy cell C1 to, say E1 (and adjust the CF for E1 to reference A1) then the original CF for C1 will work correctly AND all the CF in C2 to C100 will also work correctly - If I delete E1 everything stops again.

Calculation Options on the Formulas tab is set to Automatic and, following advice found in a similar thread on Mr Excel [thanks to Georgiboy], I have checked the following - it was already set to True:

1. Set up your conditional formatting in the cell's you want.​

2. Make sure you activate the "Developer" tab in the menu.​

3. Do this by clicking on the Office button, click on Excel options and in the "Popular" section check the box ""Show developer tab in the Ribbon".​

4. Click on the Developer menu and go to the Visual basic view.​

5. Click on the menu View and select "properties Window". In that list you can find the "EnableFormatConditionsCalculation".​

6. Set this value to "True" and the auto refresh will work.​

7. Perform this action for all tabs in your sheet.​

This used to work - it stopped sometime in the last three weeks (the spreadsheet was last used 3 weeks ago)

Thanks in advance for any help

Regards

Netrix

 

[6StringJazzer]

When asking for help with a conditional formatting rule, it is helpful to show the conditional formatting rule.

 

[netrixuser]

When asking for help with a conditional formatting rule, it is helpful to show the conditional formatting rule.

Hi Jeff,

I didn't post the formatting rule because the rule is working - it just doesn't work automatically. I have to "force" the sheet to update but doing any of the things mentioned in the OP

regards

Netrix

 

[netrixuser]

When asking for help with a conditional formatting rule, it is helpful to show the conditional formatting rule.

In case it will help - here are the CF rules, as previously mentioned - these rules are working fine but do not update automatically. I have to either widen the column or the row, or click onto another tab and back again before the CF updates.