Conditional Formatting not correctly displaying upon spreadsheet open

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
I have cells with conditional formatting, specifically Icon Set based on their value. The value thresholds for each colour (depicted below) are on another sheet, and for some reason this kinda breaks the formatting display. When I open the file, they're all green. If I edit one of the cells and hit enter, it refreshes the display and they correctly display again. I assume the cell edit is prompting the spreadsheet to recalculate values and reassess the conditional formatting.

I've checked the "EnableFormatConditionCalculation" property for both sheets and it is set to True (this was a suggestion I found on another website). Toggling it from False to True will also prompt the formatting to correctly display.
1599102213131.png


This is only a problem while having the thresholds located on a different sheet. If I cut and paste those thresholds to the sheet with the formatted cells, I don't have this problem. But I don't really want the thresholds on the same sheet. I have a sheet with a whole bunch of validation data etc. where I like to keep these things, so I'm hoping someone will know an easy fix.

Formatting value thresholds:
1599098726435.png


Incorrect formatting on file open, before editing a cell:
1599101768888.png


Correct formatting after editing a cell:
1599101795544.png
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Is the calculation set to Automatic when the workbook is opened and is it the only workbook open.
 

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
Is the calculation set to Automatic when the workbook is opened and is it the only workbook open.

Yes and yes.

Interestingly "Calculate Now" doesn't update the formatting, but "Calculate Sheet" does.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do you have any circular references on other sheets?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I am assuming that there is no vba in the workbook either in a regular module, ThisWorkBook or the sheet?
 

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167

ADVERTISEMENT

I am assuming that there is no vba in the workbook either in a regular module, ThisWorkBook or the sheet?
No none whatsoever.

The cells that are conditionally formatted are within a Table, but I've converted it to a range, saved, closed and reopened and the problem persists, so I assume that isn't related.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Can you go to your developer tab and open the VBE, then in the project window find the workbook and click on the sheet name that has the conditional formatting.
Now have a look in the Properties window and check if EnableFormatConditionsCalculation is set to True or False.

Try setting all the sheets in the workbook to true
 
Last edited:

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
Yep can confirm they're all True.

If there's anything useful in the ThisWorkbook properties, here's a snip of those:

1599545232375.png
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,496
Members
410,840
Latest member
Kar3ousse
Top