We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
363 Views
Last Modified: 2012-05-07
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2015

Commented:
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...
Saurabh...
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2011

Commented:
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.

Regards,
Chris
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range
        
        Me.Parent.ResetColors
        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
        Next
End Sub

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.