Conditional Formatting not correctly displaying upon spreadsheet open

[NiMip]

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.

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:

Incorrect formatting on file open, before editing a cell:

Correct formatting after editing a cell:

 

[MARK858]

Is the calculation set to Automatic when the workbook is opened and is it the only workbook open.

 

[NiMip]

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]

Do you have any circular references on other sheets?

 

[NiMip]

Do you have any circular references on other sheets?

None detected under Error Checking

 

[MARK858]

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

 

[NiMip]

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]

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

 

[NiMip]

Yep can confirm they're all True.

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