I have a excel speed sheet here I'm constantly adding rowns of
Information, I have added some drop down list to speed up data entry
and I would like to have a script or function that will automatic
change the color to the current row base on my choice from the drop
down list, the list goes something like this Coolum A "reference Number"
Colum B "date" column C "Customer Names" column D "Agent Name" - Column D
has a drop down list with the names of all agents. I would like that
when you choose let say agent 'Pablo' the whole Row Turn to a Blue
cell shading when you choose 'Maria' from the drop down list the
Whole row turn to a pink Cell Shading and so on, I think you got the
Idea, if you need my speed shett to work on it I can email to you

---

Clarification of Question by pablo_g-ga on 27 Jul 2004 18:02 PDT

you can donwload the file here 
http://www.tonerdepot.org/file.xls

You're probably aware that Excel's "Conditional Formatting" function
(under the "Format" menu) can do what you're looking for; however, it
allows a maximum of three conditions (thus you could only change the
row color for up to three names).

To get around this, I did some searching, and found a perfect resource
at the following URL (whose author I would like to cite, but I
couldn't find his/her name):
     http://rhdatasolutions.com/ConditionalFormatVBA/

Using his basic code, this is what we can add to your spreadsheet to
accomplish what you want:


Step 1: Add color reference column
==================================
I. In your "Companies" sheet tab, insert a column after the names. 
Thus, There will be a blank column between C (names) and E (type).

II. Next to each name, enter a color digit of your choice (e.g. 3,4,5,
etc.), each of which corresponds to a color (red, green, blue, etc.
respectively).  You can get a list of color digits to colors at
     http://www.mvps.org/dmcritchie/excel/colors.htm .
Use the "font" column in the table provided on that page to find the
digits.  So, columns C and D of the Companies sheet should now read:
    Pablo      3
    Carolina   8
    Milena     27
etc.

III. Just like you've done for other range names for the "coverage",
"Keywords", "names", and "type" drop-down lists, select the entire
columns of C and D and create a range name for them called
"nameColors".  (e.g. select C&D, and then go Insert: Name: Define).


Step 2: Add Macro to main sheet to change colors
================================================
I. Go back to the "Binder Book" sheet tab, and then right-click on the
tab at the bottom and choose "View Code"

II. In the blank macro page that comes up, enter the following
(excluding the hyphens at the top and bottom (note that this is
primarily from that site listed at the first URL, though I have
modified it for your specific spreadsheet and have altered it to
change an entire row's color, not just a single cell):

-------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

    Dim rng As Range

    Set rng = Intersect(Target, Range("L:L"))
    ' The L:L listed is the column which has the drop-down for the agents.
    ' If you change it to another column, you must modify the L:L to reflect
    ' the new column letter.
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim cl As Range
        For Each cl In rng
            On Error Resume Next
            ' -- The line above won't change the cell's background
            ' -- color if the cell's value is not found in the range
            ' -- that we specified (rngcolors).
            cl.EntireRow.Interior.ColorIndex = _
                Application.WorksheetFunction.VLookup(cl.Value _
                , ThisWorkbook.Sheets("Companies").Range("nameColors"),2,False)

            If Err.Number <> 0 Then
                cl.EntireRow.Interior.ColorIndex = xlNone
            End If
        Next cl
    End If

End Sub
-------------------

III. Save and close the Microsoft Visual Basic macro editor window in
which you entered this information, and then save your spreadsheet. 
Now, when you choose a different name from the drop-down box in column
L ("Memo") in the "Binder Book" main sheet, the entire row should
change color.


Please let me know if you have any questions.

--Joey

---

Clarification of Answer by joey-ga on 27 Jul 2004 23:51 PDT

I forgot my searching strategy:  "conditional formatting" excel "more than 3"

---

Request for Answer Clarification by pablo_g-ga on 28 Jul 2004 08:33 PDT

the awnser is great it works all most perfect look at the file and you
see what is happening, the green row is the expected effect and the
blue row is what happens, I'm sure is just a simple change in code
will do, if you could fix it would be great thank you


http://www.tonerdepot.org/file.xls

---

Clarification of Answer by joey-ga on 28 Jul 2004 13:06 PDT

Hey there.  You seem to have changed your Excel file a little from the
original one you posted. ;-)

Because the "Memo" column is now full of merged cells (covering two
rows), that changes things a little.

The fix is easy, though.  Just change the line that says:
     cl.EntireRow.Interior.ColorIndex = _
to:
     cl.MergeArea.EntireRow.Interior.ColorIndex = _

AND, change the line that says:
     cl.EntireRow.Interior.ColorIndex = xlNone
to:
     cl.MergeArea.EntireRow.Interior.ColorIndex = xlNone


Or, if you'd prefer to just copy and paste the entire updated code, here it is:

-----------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

    Dim rng As Range

    Set rng = Intersect(Target, Range("L:L"))
    ' The L:L listed is the column which has the drop-down for the agents.
    ' If you change it to another column, you must modify the L:L to reflect
    ' the new column letter.
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim cl As Range
        For Each cl In rng
            On Error Resume Next
            ' -- The line above won't change the cell's background
            ' -- color if the cell's value is not found in the range
            ' -- that we specified (rngcolors).
            cl.MergeArea.EntireRow.Interior.ColorIndex = _
                Application.WorksheetFunction.VLookup(cl.Value _
                , ThisWorkbook.Sheets("Companies").Range("nameColors"),2,False)

            If Err.Number <> 0 Then
                cl.MergeArea.EntireRow.Interior.ColorIndex = xlNone
            End If
        Next cl
    End If

End Sub
-----------------

tahnk you very much  fantastic explenation