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

This is a perfect example of where you should use the macro recorder. Turn on the recorder and set the color of the cells through the UI. Stop the recorder and review the macro. It will generate a bunch of extraneous code, but it will also show you syntax that works for what you are trying to accomplish. Strip out what you don't need and modify (if you need to) what's left.

Answer 3

Do a quick 'record macro' to see the color number associated with the color you're looking for (yellow highlight is 65535). Then erase the code and put

Sub Name()
Selection.Interior.Color = 65535 '(your number may be different depending on the above)
End Sub

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.

Answer 5

It doesn't work if you use Function, but works if you Sub. However, you cannot call a sub from a cell using formula.