Solved

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

Posted on 2009-07-09
4
343 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 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

10 Experts available now in Live!

Get 1:1 Help Now