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

Question

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

This may sound stupid, but did you F9 to refresh? May not work, but sometimes I like to cover all the bases :) — socool111
– socool111, Commented Jun 18, 2014 at 13:51
No it doesn't work. The background color still stay white. Thanks for you reply. — Strall
– Strall, Commented Jun 18, 2014 at 14:01
Do you use application.calculation = xlcalculationmanual at all? Try the opposite: application.calculation = xlcalculationautomatic — ggle
– ggle, Commented Jun 18, 2014 at 20:15
i only use application.calculation = xlcalculationautomatic and it's still not udating the bg color. — Strall
– Strall, Commented Jun 19, 2014 at 7:27

Strall · Accepted Answer · 2014-06-19 16:04:44Z

1

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.

answered Jun 19, 2014 at 16:04

Strall

216 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Przemyslaw Remin Over a year ago

Cells.ApplyOutlineStyles you missed t

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related