Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-07-09
4
Medium Priority
?
352 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.
0
Comment
Question by:techlinden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24815244
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...
0
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 24815772
You code use a macro like this example below.

jppinto
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"
                cll.Select
                Selection.Interior.ColorIndex = 16
            Case "Elvis"
                cll.Select
                Selection.Interior.ColorIndex = 25
            End Select
        Next cll
    End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24816027
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

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question