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:
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: