VBA: Change a Cells Background Color

September 22nd, 2004 | Categories: Cells, Columns & Rows | Tags:

To change a cells background color using VBA you can use the interior.colorindex property. Here’s a couple ways to change the background color of cell A1.

An example using the Range() method:

Range("A1").Interior.ColorIndex = 5

An example using the Cells() method:

Cells(1, 1).Interior.ColorIndex = 15

Need an easy way to determine what number equals what color? Check out Color Reference For Colorindex.


Leave a comment | Trackback
  1. Mac Chris
    January 29th, 2009 at 15:38
    Reply | Quote | #1

    the sub (macro) is perfect
    but the function goes failed!
    WHY???

    Sub ColorRef()
    Dim x As Integer

    For x = 1 To 56

    If x 10 Then
    CurrentCell.Interior.ColorIndex = 3
    Else
    CurrentCell.Interior.ColorIndex = 11
    End If
    End Function

  2. Gordon
    February 10th, 2009 at 20:11
    Reply | Quote | #2

    @Mac Chris

    You start off defining a subroutine (Sub) and end up closing a function. This alone will not work.

    You appear to be expecting to loop through a range of cells, but there is no code to do this.

    Following your logic, is something like this what you were trying to achieve?

    Sub changeBackColor()
    Dim x As Long

    For x = 0 To 55
    If x = 10 Then
    Sheets(“Sheet1″).Range(“A1″).Offset(x, 0).Interior.ColorIndex = 3
    Else
    Sheets(“Sheet1″).Range(“A1″).Offset(x, 0).Interior.ColorIndex = 11
    End If
    Next x
    End Sub