Conditional formatting is buggy - applying shading to cells

Dear @Elias

Thank you for posting your question in the Microsoft Q&A forum. 

I understand you're experiencing an issue where conditional formatting in Excel no longer updates automatically when the value in a referenced cell change. Based on your description, this behavior may be related to Excel's calculation settings or a recent update. 

To help me narrow down the cause, could you please provide the following details? 

• What version of Excel are you using? 
• Are there any specific add-ins or macros running? 
• Have you encountered this issue in other workbooks as well, or just the current one? 

In the meantime, please try the following official troubleshooting options which usually resolve issues with basic issues: 

Option 1: Check Calculation Mode: 

• Ensure that Excel is set to Automatic Calculation:  
• Go to Formulas > Calculation Options > Select Automatic. 
• If set to Manual, conditional formatting may not update until the sheet is recalculated. 

Selecting "Automatic" should immediately restore the instant-update functionality for your conditional formatting. 

For more detailed information on this feature, you can refer to the official Microsoft Support article on the topic: Change formula recalculation, iteration, or precision in Excel 

Option 2: Stale Value Formatting (Excel Version 2409 or later): 

• Excel now includes a feature that marks cells with outdated values (stale) when in Manual or Partial Calculation mode. 
• You can enable or disable this feature via Formulas > Calculation Options > Format Stale Values. 

For your reference, here is the official Microsoft documentation on this feature: Stale Value Formatting in Excel

Option 3: Compatibility Issues: 

• If the workbook was saved in an older Excel format (e.g., Excel 97-2003), some conditional formatting features may not function correctly. 

• Review compatibility settings via File > Info > Check for Issues > Check Compatibility. 

More information: Conditional formatting compatibility issues for Excel - Microsoft Support 

I truly hope you understand that the initial steps I suggested may not have completely resolved the issue, as there might still be underlying causes, we haven't uncovered yet. Please know that I’m here with you every step of the way, ready to work together and do everything I can to help troubleshoot and find the best solution. 

If the problem persists after trying these steps, please let me know your version of Excel and whether the workbook uses external references or macros so I can assist you further. 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

thank you for your response - fixed it with this VBA code:
Private Sub Workbook_Open()

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

which is basically what you suggested