Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Location
    Northern California
    Posts
    21

    Unanswered: Formatting a cell based on the condition of another cell

    This is more a plain Excel question without any database aspect.

    I'm using Excel 2000 at work. I have a spreadhsheet that lists the skill sets of the various techs, but also is used to show who is in for the day (or morning, afternoon.) A triage team uses it to assign trouble tickets to the techs. It also lists their current number of tickets (as of the most recent update.) We assign based on Skillset, availability, and workload.

    It's updated at least daily and saved as HTML so that it can be viewed by multiple people and refreshed quickly, while I can edit it on the fly without "locking" issues.

    I use colour codes, filling the cell of the tech's name, to indicate availability. Say, no-fill is available all day, Orange is out all day, blue is out for morning, and green is out for afternoon.

    I used to just sort by the number of tickets. This makes it so that people who are out are scattered throughout the list. I thought I could use a letter-code in another column for sorting availability, but still keep the colours for quick visual pick-up.

    So now, I have 'A' and no-fill for available all day, 'M' and blue for out for morning, 'P' and green for out for afternoon, and 'O' and orange for out all day.

    What I would like is for the colour fills on the rep names to update automatically based on what letter is in the Availability column. I looked into conditional formatting, and there is a formula option, but I couldn't figure it out. Though I'm not adverse to using code, I only have a basic understanding of using code in Access. I do not know how to address things in Excel. So, I would need a bit of hand-holding and walked through from the very beginning.

    Can anyone help?

    Thanks.
    Attached Files Attached Files
    Cor
    {Currently working with Access 97, so most questions will be from that point-of-view}

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    You can do this with conditional formatting, but it can get messy. And if you want more conditions you are stuck. So what I have done for my own work, and have set it up for you, is to use VBA, in the Worksheet module. It will automatically check column C and determine how many rows are involved, and will automatically format each cell in Column C based on you conditions. So no matter how many rows you have, it will color, no matter how many conditions, you can add another "Case" in the code.

    Here is the code (you can see it on your sheet if you right click on the worksheet (tab) name, and choose "View Code".

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Conditional Formatting for more than 3 conditions
        Dim rng As Range
        Dim LastRow As Long
        ' Target is a range::therefore,it can be more than one cell
        ' For example,,someone could delete the contents of a range,
        ' or someone could enter an array..
        LastRow = Range("C65356").End(xlUp).Row
        Set rng = Intersect(Target, Range("C4:C" & LastRow))
        If rng Is Nothing Then
            Exit Sub
        Else
            Dim cl As Range
            For Each cl In rng
                Select Case cl.Value
                    Case "a"
                        cl.Interior.ColorIndex = 2
                    Case "m"
                        cl.Interior.ColorIndex = 39
                    Case "o"
                        cl.Interior.ColorIndex = 44
                    Case "p"
                        cl.Interior.ColorIndex = 35
                    Case Else
                        cl.Interior.ColorIndex = 2
                End Select
            Next cl
        End If
    End Sub
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    As I re-read the post, you want the name to be colored. Note, this changes with changes in Column C.

    Here is the changed code (using Offset) to color the name.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Conditional Formatting for more than 3 conditions
        Dim rng As Range
        Dim LastRow As Long
        ' Target is a range::therefore,it can be more than one cell
        ' For example,,someone could delete the contents of a range,
        ' or someone could enter an array..
        LastRow = Range("C65356").End(xlUp).Row
        Set rng = Intersect(Target, Range("C4:C" & LastRow))
        If rng Is Nothing Then
            Exit Sub
        Else
            Dim cl As Range
            For Each cl In rng
                Select Case cl.Value
                    Case "a"
                        cl.Offset(0, 1).Interior.ColorIndex = 2
                    Case "m"
                        cl.Offset(0, 1).Interior.ColorIndex = 39
                    Case "o"
                        cl.Offset(0, 1).Interior.ColorIndex = 44
                    Case "p"
                        cl.Offset(0, 1).Interior.ColorIndex = 35
                    Case Else
                        cl.Offset(0, 1).Interior.ColorIndex = 2
                End Select
            Next cl
        End If
    End Sub
    Sorry for the confusion.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jul 2005
    Location
    Northern California
    Posts
    21
    Shades;

    Actually, you were probably more correct the first time, I want the Cells coloured (the Fill,) not the text. I'll have to look closely at your code to see how to apply the way I'm looking for. What I want, and probably didn't explain clearly, is to have the entry letter in C3 affect the Fill colour of C3, D3, and E3 (the Cell addresses are just examples.) Your first reply sounded like it might not be quite like that, but by showing me how to address the cells with code, I can probaly work the rest out if you're code isn't quite the solution. And if it is - great!

    So, either way, thanks.
    Cor
    {Currently working with Access 97, so most questions will be from that point-of-view}

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Actually, the second code needs to be adapted. This will now color the cells in Columns C, D, and E, based on the entry in Column C. Now, note, to make this work the first time, after you put this code in, then go to Excel and copy Column C data and Paste Special Values, this will initiate the change code. After that anytime you add a value in Column C (cell C4 or below) it will modify the cells.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Conditional Formatting for more than 3 conditions
        Dim rng As Range
        Dim LastRow As Long
        Dim rngColor As Range
        ' Target is a range::therefore,it can be more than one cell
        ' For example,,someone could delete the contents of a range,
        ' or someone could enter an array..
        LastRow = Range("C65356").End(xlUp).Row
        Set rng = Intersect(Target, Range("C4:C" & LastRow))
        If rng Is Nothing Then
            Exit Sub
        Else
            Dim cl As Range
            For Each cl In rng
                Select Case cl.Value
                    Case "a"
                        Set rngColor = cl.Resize(, 3)
                        rngColor.Interior.ColorIndex = 2
                    Case "m"
                        Set rngColor = cl.Resize(, 3)
                        rngColor.Interior.ColorIndex = 39
                    Case "o"
                        Set rngColor = cl.Resize(, 3)
                        rngColor.Interior.ColorIndex = 44
                    Case "p"
                        Set rngColor = cl.Resize(, 3)
                        rngColor.Interior.ColorIndex = 35
                    Case Else
                        Set rngColor = cl.Resize(, 3)
                        rngColor.Interior.ColorIndex = 2
                End Select
            Next cl
        End If
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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