Multiple Cell Background Colors in Excel Spreadsheets

Question

Excel has a Conditional Formatting... option under the Format menu that allows you to change the style/color/font/whatever of a cell depending upon its value. But it only allows three conditions.

How do I get Excel to display say, six different background cell colors depending upon the value of the cell? (IE Make the cell red if the value is "Red", and blue if "Blue" etc.)

Answer 1

You will need to write something in VBA.

See example here: Get Around Excels 3 Criteria Limit in Conditional Formatting:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

    If Not Intersect(Target, Range("A1:A10")) is Nothing Then

        Select Case Target

            Case 1 To 5
                icolor = 6
            Case 6 To 10
                icolor = 12
            Case 11 To 15
                icolor = 7
            Case 16 To 20
                icolor = 53
            Case 21 To 25
                icolor = 15
            Case 26 To 30
                icolor = 42
            Case Else
                'Whatever
        End Select

        Target.Interior.ColorIndex = icolor
    End If
End Sub

Answer 2

Excel 2007 allows more than three conditions. Quoting from this Microsoft page:

EDIT: Ah, there's a "feature" in the linking code: parentheses in a link cited in parentheses aren't being handled correctly. That link is: http://msdn.microsoft.com/en-us/library/bb286672(office.11).aspx

Other benefits of the changes to conditional formatting in Excel 2007 are the ability to specify more than three conditions, to reorder conditions, and to have more than one condition resolve to True.

Otherwise. you're stuck with messy alternatives as described, I'm afraid.