Excel Macro for Background Color

I was writing an Excel Macro and I needed to set the background color of a cell based on the text value. To do this I wanted to use Conditional Formatting, and I found out that you cannot (AFAIK) setup Conditional Formatting from inside of a macro. But, you can create a macro that loops through a range and sets the formatting.

Here is how:

1. Open the VB editor (Alt + F11)
2. Open ThisWorkbook under Microsoft Excel Objects
3. Add the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Set myRange = Range("C4:C104")

    For Each Cell In myRange

        If Cell.Value = "n" Then

            Cell.Interior.ColorIndex = 3

        End If

    Next

End Sub

Anytime  a change is made to the workbook, the macro is called. It checks a range of cells, and then if the text is 'n' it sets the background color to 3. But what is '3'?

Excel uses an object called ColorIndex which takes an integer as its argument. The integer is one of 56 colors, given by the following table (a link to the source is below):

[Color 0]	[Color 0]		[Color 15]	[Color 15]		[Color 30]	[Color 30]		[Color 45]	[Color 45]
[Color 1]	[Color 1]		[Color 16]	[Color 16]		[Color 31]	[Color 31]		[Color 46]	[Color 46]
[Color 2]	[Color 2]		[Color 17]	[Color 17]		[Color 32]	[Color 32]		[Color 47]	[Color 47]
[Color 3]	[Color 3]		[Color 18]	[Color 18]		[Color 33]	[Color 33]		[Color 48]	[Color 48]
[Color 4]	[Color 4]		[Color 19]	[Color 19]		[Color 34]	[Color 34]		[Color 49]	[Color 49]
[Color 5]	[Color 5]		[Color 20]	[Color 20]		[Color 35]	[Color 35]		[Color 50]	[Color 50]
[Color 6]	[Color 6]		[Color 21]	[Color 21]		[Color 36]	[Color 36]		[Color 51]	[Color 51]
[Color 7]	[Color 7]		[Color 22]	[Color 22]		[Color 37]	[Color 37]		[Color 52]	[Color 52]
[Color 8]	[Color 8]		[Color 23]	[Color 23]		[Color 38]	[Color 38]		[Color 53]	[Color 53]
[Color 9]	[Color 9]		[Color 24]	[Color 24]		[Color 39]	[Color 39]		[Color 54]	[Color 54]
[Color 10]	[Color 10]		[Color 25]	[Color 25]		[Color 40]	[Color 40]		[Color 55]	[Color 55]
[Color 11]	[Color 11]		[Color 26]	[Color 26]		[Color 41]	[Color 41]		[Color 56]	[Color 56]
[Color 12]	[Color 12]		[Color 27]	[Color 27]		[Color 42]	[Color 42]
[Color 13]	[Color 13]		[Color 28]	[Color 28]		[Color 43]	[Color 43]
[Color 14]	[Color 14]		[Color 29]	[Color 29]		[Color 44]	[Color 44]

A more complex example is shown below. This is used in a ToDo sheet. It checks the "Done" column (C) to see if  the cell is marked 'y' or 'n' and sets the corresponding color. If the task is done (C='y') then it also sets the cell adjacent tothe color gray with strikethrough text.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Set myRange = Range("C4:C104")

    For Each Cell In myRange

   

        If Cell.Value = "n" Then

            Cell.Interior.ColorIndex = 3

        End If

        If Cell.Value = "y" Then

            Cell.Interior.ColorIndex = 10

        End If

        If Cell.Value <> "n" And Cell.Value <> "y" Then

        Cell.Interior.ColorIndex = xlNone

        End If

       

        ' add strikethrough

        Dim myRow As Integer

        Dim myCol As Integer

        myRow = Cell.Row

        myCol = Cell.Column - 1

       

        If Cell.Value = "y" Then

            Cells(myRow, myCol).Font.Strikethrough = True

            Cells(myRow, myCol).Interior.ColorIndex = 15

        End If

       

        If Cell.Value = "n" Then

            Cells(myRow, myCol).Font.Strikethrough = False

        End If

    Next

End Sub

Happy coding :)

[Source MVPS site]