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, NateO

[ 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