in excel, how do i color code (conditional formating)

in excel, how do i color code (conditional formating) so each name has it's own color.  it only allows me to add up to three conditional formating and i have 7 names.
Who is Participating?
jppintoConnect With a Mentor Commented:
You code use a macro like this example below.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
        Set sh = ActiveSheet
        Dim cll As Range
        For Each cll In sh.UsedRange
            Select Case cll.Value
            Case "John"
                Cells(cll.Row, cll.Column).Select
                Selection.Interior.ColorIndex = 6
            Case "Paul"
                Selection.Interior.ColorIndex = 16
            Case "Elvis"
                Selection.Interior.ColorIndex = 25
            End Select
        Next cll
    End Sub

Open in new window

Saurabh Singh TeotiaCommented:
In case if you want to do more then 3 conditional formating, You need to do it with the help of macro, Since excel like you already aware about dont permit more then 3 conditions.
If you can upload your sample file, It will be easy to provide you a solution for this...
Chris BottomleyCommented:
Hello techlinden,

The following code sits in the worksheet code page and sets/clears the color as appropriate.  Note it resets the palette to ensure consistent operation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range
        Set rng = Intersect(Target, Me.Range("a1:c3"))
        If rng Is Nothing Then Exit Sub
        For Each cel In rng.Cells
            Select Case cel.Value
            Case "A"
                cel.Interior.ColorIndex = 14
            Case "BB"
                cel.Interior.ColorIndex = 13
            Case "CCC"
                cel.Interior.ColorIndex = 12
            Case "DDDD"
                cel.Interior.ColorIndex = 11
            Case Else
                cel.Interior.ColorIndex = -4142
            End Select
            Debug.Print cel.Interior.ColorIndex
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.