Conditional Format and Data Validation question

[lillian]

Hi all,

I am looking for a way to apply conditional formatting in 3 cells in a row (see below), based on a dropdown selection in another column (notes, below). I'd use conditional formatting, but I have 12 conditions. I'd use an add-in, but I have over 10k cells to effect.

ex.
time start --- total --- time end --- notes
8am 10 6pm [drop down]

Here's the code I've been able to cobble together with the conditions I want to effect the cells, but don't know how to tie the whole code together. As it is, I can't even get Excel to recognize this sub.

The code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveCell
Select Case .Value

Case "Overtime"
With .Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "Additional hours"
With .Interior
.ColorIndex = 46
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "sick"
With .Interior
.ColorIndex = 34
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "sick (family member)"
With .Interior
.ColorIndex = 39
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "vacation"
With .Interior
.ColorIndex = 17
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "non-working holiday"
With .Interior
.ColorIndex = 22
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "personal day"
With .Interior
.ColorIndex = 36
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "non-working holiday"
With .Interior
.ColorIndex = 22
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "ed day"
With .Interior
.ColorIndex = 0
.Pattern = xlLightDown
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "shift trade"
With .Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "1.5x shift"
With .Interior
.ColorIndex = 45
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "not working"
With .Interior
.ColorIndex = 5
.Pattern = xlLightHorizontal
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

Case "on call"
With .Interior
.ColorIndex = 17
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1

End Select
End With
End Sub

Any input is welcome.

 

[daniels012]

Does it have to be a color?? I have used a symbol for a similar task and it worked fine. HOw are you going to look at the data are you going to sort them or maybe look at all the ones with overtime? etc.etc.

 

[lillian]

Thanks Daniel012!

The column will allow a sort, the applied format will provide a strong visual. I'm going with the original colors and patterns - but if you have another system, I'm game to try it.

-edit for clarity-

I will look at data over time & rate of incidence. The column is a drop down of the cases.

 

[Tazguy37]

The following code will work, but where is the dropdown?

If I know its location, I can change the code so that no other changes to the worksheet effect these color changes. Know what I mean?

For example, this would work for Column H:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Columns("H")) Is Nothing Then Exit Sub
    Application.EnableEvents = False

    With Target
        Select Case .Value
            Case "Overtime"
            
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 40
                    .Pattern = xlSolid
                End With

            Case "Additional hours"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 46
                    .Pattern = xlSolid
                End With

            Case "sick"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 34
                    .Pattern = xlLightUp
                End With

            Case "sick (family member)"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 39
                    .Pattern = xlLightUp
                End With

            Case "vacation"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 17
                    .Pattern = xlLightUp
                End With

            Case "non-working holiday"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 22
                    .Pattern = xlLightUp
                End With

            Case "personal day"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 36
                    .Pattern = xlLightUp
                End With

            Case "non-working holiday"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 22
                    .Pattern = xlLightUp
                End With

            Case "ed day"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 0
                    .Pattern = xlLightDown
                End With
            
            Case "shift trade"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 0
                    .Pattern = xlGray8
                End With

            Case "1.5x shift"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 45
                    .Pattern = xlSolid
                End With
    
            Case "not working"
                With .Offset(, 1).Resize(, 3).Interior
                    .ColorIndex = 5
                    .Pattern = xlLightHorizontal
                End With
    
            Case "on call"
                With .Offset(, 1).Resize(, 3).Interior
                    .Offset(, 1).Resize(, 3).ColorIndex = 17
                    .Pattern = xlSolid
                End With
        End Select
        .Font.ColorIndex = 1
        .Interior.PatternColorIndex = xlAutomatic
    End With

    Application.EnableEvents = True
End Sub

Note also, that this is a Worksheet_Change() Event, not a _SelectionChange() event, as your original code states. Let me know if this approach doesn't work for you, or any questions you have.

Hope that helps!

EDIT: Almost forgot: Since you're looking for trends, did you want to count each instance of each case? The COUNTIF() function would work nicely, if you're only counting once condition.

 

[lillian]

TazGuy!

Hi there TazGuy,

The column is M & the offset is 4 cells to the left of M (I-L). I've added your code, but am running into an odd problem that is becomming familiar... When I hit the run Macro button, it doesn't call up the sub Worksheet_Change. I've updated the fields with a smackeral of the drop down results, but no luck.

I'm sure I'm doing something off - and will try again.

As to the reporting I'm looking for -- countif would work a treat, I'm looking for all instances, vs. just one.

VBA is very fun!

 

[Tazguy37]

Re: TazGuy!

When I hit the run Macro button, it doesn't call up the sub Worksheet_Change. I've updated the fields with a smackeral of the drop down results, but no luck.

Ohhhh, you want to run from a button!

What I did was make it so that any time you made a selection from the dropdown, the code would fire.

If you still want to use a button to make this work, do you want only the active cell's row to have these color changes, or all the cells in Columns I-L where Column M has a value? Or something else?

I just figured it was easier with a _Change() event because you'd see the changes instantly (or nearly).

Which would you prefer? It wouldn't be hard to make any of those option work, really.

Cheers!

 

[lillian]

oh my!

Ok, I'm still learning & so didn't get it that the code would fire when the cell was populated. eep!

Event change works just fine!

 

[lillian]

Not seeing format updates

Hmmm - usually this is an error between my keyboard & the floor, but I'm not seeing the formats update.

maybe I'm missing something?

-edit---

I've tried event change for a bit & am staring down the status bar. I've removed any formulas that make excel look through more than 65000+ cells to stop it from calculating data over & over again, but because I'm using auto-filter for most of my sorting needs, I'm seeing calculate in the status bar all the time (since I've unchecked "iteration" in the calculate tab of tools>options.

would using a button to call the command minimize the calculations? Is it even calculating when it's reviewing cells for specific criteria?

 

[Tazguy37]

Re: Not seeing format updates

Hmmm - usually this is an error between my keyboard & the floor, but I'm not seeing the formats update.

maybe I'm missing something?

-edit---

would using a button to call the command minimize the calculations? Is it even calculating when it's reviewing cells for specific criteria?

I didn't realize you had so much data. Try adding these lines:

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

Obviously, the EnableEvents lines are already in the code, just add the ones for Calculation. You might also add lines for Application.ScreenUpdating (False at the beginning, and True at the end).

Shoot me a PM (and post to the thread as well) if you're still having problems with this, after you make these changes.

 

[lillian]

still no luck

Thanks TazGuy - I've added the code & didn't realize that the # of cells had grown so big until I did a little digging on calculate.

I'm heading out now for a bit, but will PM you with the details - basically, the dropdown is there & functioning, but no formatting is being applied.