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.

Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
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