MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Can you color code a Validation List

  Asked By: James    Date: Dec 23    Category: MS Office    Views: 1208

I have been teaching myself Excel
functions as I needed them for work, but I'm stumped. I've got a drop
down Validation List for the managers in the district to choose from,
but I was hoping to color code their drop down choices to make it
easier for high level review. Only problem is there are more than
three choices so I can not use conditional formatting as I understand
it. Can anyone suggest a solution or is this just impossible in
Excel? Thank you in advance for any and all help.



4 Answers Found

Answer #1    Answered By: Rachel Fischer     Answered On: Dec 23

This is certainly not impossible. I am assuming that what we are talking
about is colourcoding the cell when the choice has been made.

You would need to use the onchange event of the sheet, test it to see if it is
that cell that has changed, then, if it has you would colour the cell however
you wanted to.

One way of doing this woould be to use the validation  table and put the color
name in the cell to the right of each person, then you could do a
worksheetfunction.vlookup to get the colour name.

We may be able to help  you further but need a bit more info:

How many cells are there which use the validation method. Where are they.
How would your color  scheme be worked? i.e. all individual colors, groups, or
How many distinct colors do you need.

Answer #2    Answered By: Shannon Johnson     Answered On: Dec 23

I can not thank you enough for getting back so quickly. I will try to answer
all your questions. There are thirteen menu items in the list. They are on the
same spreadsheet as the lists, I just hid their rows a few lines below the main
data section to make it easier for me. There are nine distinct colors I wanted
to use. Five items on the list  share one color  and the rest have unique colors
tied to them. There are up to 264 cells all linked to this same validation
list, but they have no relationship to each other. I have multiple business
units reporting activities completed each week based on this validation  list of
tasks. I hope this helps you in understanding my issue better.

On a much more ambitious note, once the above works is there any way at the
bottom of each weeks column to automate a sum of the range of the Tools $ column
that just sums the tasks of the same color? (Note the tasks and colors would be
the cells two to the left of the range that is being added).

Answer #3    Answered By: Adelgiese Fischer     Answered On: Dec 23

You put the number for each color  (eg 255 for red) you want in the cell to the
right of the validation  item in the validation source table.

Then you create a named range "colors" to include the validation items and the
color numbers to the right.

Then create a named range "datarange2" to include all the cells where the
validation method is used but not the validation table.

Then put this into the worksheet code  ( right click the worksheet tab and
click view code).

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("datarange2"), Target) Is Nothing Then
Dim requiredcolor As String
requiredcolor = WorksheetFunction.VLookup(Target.Text, Range("colors"), 2,
Target.Interior.Color = requiredcolor
End If
End Sub

The color numbers are the decimal equivalent of the hex value as follows

first 2 characters Blue
Middle two characters Green
Right two characters Red

So FF0000 is Blue and converted from Hex to decimal is 16711680

Use the calculator function to convert.

As regards part 2 you might need to give me a bit more info. It is certainly
feasible but I need to know where the cells, color numbers, totals etc are.
In essence you would probably need a function which looks at each cell,
evaluates its interior.color, and then counts it.

Answer #4    Answered By: Oliver Jones     Answered On: Dec 23

Here are two functions from Chip Pearson's site
(http://www.cpearson.com/excel/colors.htm) which provide a means for summing by
In this example, C4:C24 is the range to be summed, and A3 sontains your
dropdown validation  list, shaded in the same color  by which you want to sum.

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
Dim Rng As Range
Dim OK As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
End Function

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If
End Function

The formula to sum C4:C24 based on the color in A3 would be:

Didn't find what you were looking for? Find more on Can you color code a Validation List Or get search suggestion and latest updates.