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

User · Answer

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.

User · Answer

You can use either   ActiveCell Interior ColorIndex   28   or   ActiveCell Interior Color   RGB 255 0 0

User · Answer

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

User · Answer

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

User · Answer

You can use either   ActiveCell Interior ColorIndex   28   or   ActiveCell Interior Color   RGB 255 0 0

User · Answer

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.

User · Answer

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

User · Answer

You can use either   ActiveCell Interior ColorIndex   28   or   ActiveCell Interior Color   RGB 255 0 0

User · Answer

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

User · Answer

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.

User · Answer

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

User · Answer

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.

User · Answer

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

User · Answer

You can use either   ActiveCell Interior ColorIndex   28   or   ActiveCell Interior Color   RGB 255 0 0

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

Examples related to vba

Examples related to excel