Solved

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

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

829 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