Solved

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

Posted on 2009-07-09
4
344 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
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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now