Conditional formatting not updating

[STGE2]

Hi

I have searched all over the net and found a few threads with the same issue but not one with a solution.

My issue is I have conditional formatting controlling if dates should be printed in: mmm, yyyy og dd etc. (for a gantt chart)... this could of cause also be solved by writing a formula instead of using conditional formatting. BUT for some of the cells I need to set custom format to ;;; (to hide the text form users.. this cannot be done with a formula).

I have a drop down box to choose if dates are shown i months, weeks or days.

Problem is that when the drop down box is changed the conditional formatting is NOT updating. After changing the drop down box I have to preform 2 updates of random cells before the formatting is updates (not 1 but 2 changes which is very strange...)

The following fixes do NOT work:
1) EnableFormatConditionsCalculations is set TRUE in the VBA properties for the sheet (has no affect on above issue)
2) The sheet is of cause set to auto calculate.
3) All forms of calculations on the worksheet does not update the formatting

I have solved this by writing below function in VBA.. however I think this is a bad solution as I wanted a workbook without code + the user are not able to undo any changes in the workbook when I'm using the Worksheet_Calculate function:

Private Sub Worksheet_Calculate()
    'The conditional formatting is not updating when value of dropdown box is changed... Therefore the value is forced to update the formatting
    Application.EnableEvents = False
    Range("rgCalanderRange") = Range("rgCalanderRange")
    Application.EnableEvents = True
End Sub

(is there any way to upload the workbook so I can share it with you?)

Excel 2007

Hope there are some clever guys out there who can help with this little problem

Best regards
Stine

 

[STGE2]

Hi

I have done a lot of testing and found out it is only some of the conditional formatting which is not updating.

If I set the background color of the cells when changing value in drop down list everything is fine.

However if I also apply formatting to the cell (Number->Custom) then this part of the formatting is not updating (background is still shifting but the number format of the cell is not).

Is there no one out there who have similar problems?

Regards
Stine

 

[MargoBebb]

I have this same issue - I want users to input data, which then uses a Match function to find the answer - depending on what this is, the colour changes. BUT, if I change the inputs, or delete them, the colour of the cell does not change. Help appreciated

 

[Czar1]

I have this same issue - I want users to input data, which then uses a Match function to find the answer - depending on what this is, the colour changes. BUT, if I change the inputs, or delete them, the colour of the cell does not change. Help appreciated

Hi Margo
Luckily I was able to overcome this issue. Use the 'Use a Formula to determine which cells to format'. Even though your calculation is simple like check if value in A1 is greater than B1, do an additional check like below.

A1>B1

Instead of above use the below

A1-X1>B1

You can store the value zero (0), in X1, so it will not impact end result.

Same example with 'OR' condition is given below. It checks whether A1 is greater and B1 or A1 is smaller than C1.

=OR(A1-$X$1>B1,A1-$X$1<C1)<c1)

Thus by adding an extra check of subtraction, the conditional formatting works as it should be.</c1)