VBA / Conditional Format

[rickp]

I have a workbook that has the salepeoples names in row r. I would like the whole row reflect a color based on that column. I want to assign each sales person a different colr (there are 4 right now). The column rows are a - r. I can do a conditional format for the cells in column r but would like the whole row. Any ideas?
This message was edited by rickp on 2002-05-09 11:20

 

[Mark W.]

On 2002-05-09 10:56, rickp wrote:
I have a workbook that has the salepeoples names in row r. I would like the whole row reflect a color based on that column. I want to assign each sales person a different colr (there are 4 right now). The column rows are a - r. I can do a conditional format for the cells in column r but would like the whole row. Any ideas?

A Conditional Format can affect a the format for an entire row; however, your bigger problem is that it only supports 3 conditions! What are you gonna do for the 4th Salesperson?

 

[rickp]

I was hoping maybe a VB that would take care of the 4 sales people or maybe be able to handle more.

 

[Mark W.]

On 2002-05-09 11:07, rickp wrote:
I was hoping maybe a VB that would take care of the 4 sales people or maybe be able to handle more.

VBA would. Perhaps you should post your problem as "VBA for..." rather than "Conditional Format".
This message was edited by Mark W. on 2002-05-09 11:11

 

[NateO]

What cell is in the input, R_ ?

What are the inputs (names)? What are the colors respecitve colours? This can be done via worksheet_change.

We'll be waiting.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-09 11:35

 

[rickp]

It starts at cell R5 ends R184, the names are susan , brian , tracy , jason .. the colors are blue , green , pink , purple .

 

[NateO]

Almost there, you want:

R5 ends R184 highlighted based on which single cell that changes.

Or is it r5:ar5, r6:ar6, etc....Still which cell is the decision maker?

 

[rickp]

Column R is the decision maker ... it starts at row 5

 

[NateO]

Right-click on the sheet in question, click 'view code' and paste the following procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [r5:r184]) Is Nothing Then
    If Target.Value = "Susan" Or Target.Value = "susan" Then
    Target.EntireRow.Interior.ColorIndex = 5
    ElseIf Target.Value = "Brian" Or Target.Value = "brian" Then
    Target.EntireRow.Interior.ColorIndex = 4
    ElseIf Target.Value = "Tracy" Or Target.Value = "tracy" Then
    Target.EntireRow.Interior.ColorIndex = 7
    ElseIf Target.Value = "Jason" Or Target.Value = "jason" Then
    Target.EntireRow.Interior.ColorIndex = 21
    Else: Target.EntireRow.Interior.ColorIndex = xlNone
    End If
End If
End Sub

Might want to change Jason's color, he's a little dark (e.g., 21 to 6 or something).

Hope this helps.

 

[rickp]

Works like charm ... Yes I did change jason color to 6