Cell Interior Colour with VBA

To change the Interior colour of cells, I usually default to using the standard VBA colours.  It is easier to remember these over an index number and I usually only ever use this method for testing purposes.  I will colour a cell a particular colour to check that I am copying or referencing the value of the most appropriate cell in the VBA code I have created. It is a way to test without performing a change to the cell contents. By colouring the cells you can see what area the VBA is affecting. The following are the colours with a specific reference in VBA.

  • VBA-Defined Constants: vbYellow
  • VBA-Defined Constants: vbWhite
  • VBA-Defined Constants: vbCyan
  • VBA-Defined Constants: vbRed
  • VBA-Defined Constants: vbMagenta
  • VBA-Defined Constants: vbGreen
  • VBA-Defined Constants: vbBlue
  • VBA-Defined Constants: vbBlack

Here are some practical examples of the above.  The following will convert the used Range in Column A to Magenta.

Sub IntColour() 'Excel VBA to colour all of the used cells in column A in magenta.
Range("A1", Range("A" & Rows.Count).End(xlUp)).Interior.Color = vbMagenta
End Sub

For changing of font the following VBA can be used, notice the change at the end of the coding;

Sub FontColour() 'Excel VBA to colour thefont in green
Range("A1", Range("A" & Rows.Count).End(xlUp)).Font.Color = vbGreen
End Sub