Conditional formatting not automatically updating

[gordsky]

Hi all,
Hoping someone can point me in the right direction with this one.

Having come back from a period of leave I have found that the conditional formatting in one of my spreadsheets has stopped refreshing/updating automatically. By that I mean it works but doesnt update the screen. This is the scenario although simplified for here.

Before if I put a "Y" in say cell A1 then it would format cells B1:D4 in the desired way.
For some reason now when I put a "Y" in cell A1 nothing happens HOWEVER if I switch to another sheet and then come back to the original sheet upon returning the conditional formatting to B1:D4 is applied. Almost as if the screen updating has been turned off somehow (first thing i checked)

anyone point me towards a solution

 

[Georgiboy]

Is calculation set to automatic for the sheet in question?

 

[gordsky]

Is calculation set to automatic for the sheet in question?

hi thanks for the reply, yes calculation to automatic, screenupdating is true (as is enable events)

 

[Georgiboy]

Something else to try below:

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.

 

[gordsky]

Something else to try below:

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.

Hi checked all the above and is set as you suggest and still not working.? Totally confused by thisone

 

[netrixuser]

Hi Gordsky, did you find the cause of this ? - I have the exact same issue.
My "workaround" at present is to copy one of my cells, that has a CF that isn't updating automatically, to somewhere out of sight in the worksheet. I have to edit the CF on the new cell so it references the same "target" cell as the original CF.
This will then fix the auto update of all my cells that contain a CF - so weird ! and, like you, has me completely stumped.

 

[gordsky]

Hi Gordsky, did you find the cause of this ? - I have the exact same issue.
My "workaround" at present is to copy one of my cells, that has a CF that isn't updating automatically, to somewhere out of sight in the worksheet. I have to edit the CF on the new cell so it references the same "target" cell as the original CF.
This will then fix the auto update of all my cells that contain a CF - so weird ! and, like you, has me completely stumped.

No haven't found a solution to this one but will give what you suggest above a try

 

[Marius44]

Hello
Can you post the formula used in the conditional formatting?
Are there macros in the file that disable screen refresh? It would appear that by activating / deactivating the Sheet the conditional formatting works.
Can you specify if it is true?
Thanks and bye,
Mario

 

[gordsky]

yes by swapping sheet/tab the conditional formatting works so I was leaning toward a refresh issue but there is nothing in any macro to disable it.
The formula to trigger is simply

=$G$7=""

which then changes colour font style etc. As I say all worked fine before going on leave but has since stopped

 

[Marius44]

Hello
It is really strange and, at least for me, inexplicable.
Hello,
Mario