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.

  • 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;

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