Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VBA / Conditional Format

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Code:
    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.

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •