How do I set the background color of Excel cells using VBA?

Question

As part of a VBA program, I have to set the background colors of certain cells to green, yellow or red, based on their values (basically a health monitor where green is okay, yellow is borderline and red is dangerous).

I know how to set the values of those cells, but how do I set the background color.

Answer 1

You can use either:

ActiveCell.Interior.ColorIndex = 28

or

ActiveCell.Interior.Color = RGB(255,0,0)

Answer 2

Duplicate of Multiple Cell Background Colors in Excel Spreadsheets...

Answer 3

I need reputation to comment? Odd. Vinko's second example should be

ActiveCell.Interior.Color = RGB(255,0,0)

Answer 4

or alternatively you could not bother coding for it and use the 'conditional formatting' function in Excel which will set the background colour and font colour based on cell value.

There are only two variables here so set the default to yellow and then overwrite when the value is greater than or less than your threshold values.