Formatting a cell based on the condition of another cell

CorMak · #1 (**permalink**) 11-17-05, 18:59

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.

shades · #2 (**permalink**) 11-18-05, 10:05

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

shades · #3 (**permalink**) 11-18-05, 11:29

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.

CorMak · #4 (**permalink**) 11-22-05, 11:57

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.

shades · #5 (**permalink**) 11-22-05, 13:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech