CODEWITHSUNDEEP
excelformattingvba
Michael
Michael

Reputation: 65

Multiple Cell Background Colors in Excel Spreadsheets

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.)

Upvotes: 5

Views: 10649

Answers (4)

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

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.

Upvotes: 3

Russ Cam
Russ Cam

Reputation: 125488

put this in a module in your VBA project. You can then highlight a range in a sheet and run the sub from the Tools > Macro > Macros menu item to color each cell in the selected range. 

Public Sub ColorCells()

Dim cell, rng As Range
Dim color As Integer
Dim sheet As Worksheet

Application.ScreenUpdating = False
Application.StatusBar = "Coloring Cells"

    Set rng = Application.Selection
    Set sheet = Application.ActiveSheet

For Each cell In rng.cells

        Select Case Trim(LCase(cell))

            Case "blue"

                color = 5

            Case "red"

                color = 3

            Case "yellow"

                color = 6

            Case "green"

                color = 4

            Case "purple"

                color = 7

            Case "orange"

                color = 46

            Case Else

                color = 0
        End Select

    sheet.Range(cell.Address).Interior.ColorIndex = color

Next cell

Application.ScreenUpdating = True
Application.StatusBar = "Ready"

End Sub

If users are entering new color names into cells then you could put this in the sheet code in the VBA project to color the cells as a user is entering the color names into cells

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.cells.Count > 1 Then Exit Sub

Dim color As Integer

        Select Case Trim(LCase(Target))

            Case "blue"

                color = 5

            Case "red"

                color = 3

            Case "yellow"

                color = 6

            Case "green"

                color = 4

            Case "purple"

                color = 7

            Case "orange"

                color = 46

            Case Else

                color = 0

        End Select

Target.Interior.ColorIndex = color

End Sub

EDIT: Added Trim function around the case statement expression to test, so that accidental leading/trailing spaces in cells are ignored :)

Upvotes: 2

r1c1
r1c1

Reputation:

You can use VBA macros to do this...

here is one vba macro that might be better if need lots of cases http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/

you need to preformat 'n' cells with the way you want to format your entire range. and then use the macro in that url to get the effect.

Upvotes: 0

Galwegian
Galwegian

Reputation: 42227

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

Upvotes: 7

Related Questions