My Excel VBA conditional formatting (background color) isn't updating

1
votes

I have an application which opens an Excel workbook and in this workbook there is a sheet which has some cells with conditional formatting (background in red when 1, orange when 2 etc ...).

When there are no values in these cells and I then choose a value from a combobox, it should change the background color but it doesn't. However, when I copy and paste a value from another cell (with conditional formatting), the background color is updated and I have the same problem afterwards (background color doesn't update when I change values).

So I want to create a macro in VBA which allows this update when I change values.

N.B. There are protected cells in the workbook but no protection on these cells. N.B.2: There are 4 cells with the right background color but they are filled before or during Excel opening (it's not my application).

Application.ScreenUpdating = True 'not working
Application.Calculate             'not working too

another weird thing : if i copy/paste all the values (1,2,3,4) from a cell to another + a ctrl + z, then the conditional formatting works fine for all the cells ...

1
vbaexcel
This may sound stupid, but did you F9 to refresh? May not work, but sometimes I like to cover all the bases :) – socool111
No it doesn't work. The background color still stay white. Thanks for you reply. – Strall
Do you use application.calculation = xlcalculationmanual at all? Try the opposite: application.calculation = xlcalculationautomatic – Rhys
i only use application.calculation = xlcalculationautomatic and it's still not udating the bg color. – Strall

1 Answers

1
votes

I've found a solution using Cells.ApplyOulineStyles. It works for the first update of the values. I think i will clear the background for each change and call it again.