lillian
Board Regular
- Joined
- Oct 12, 2005
- Messages
- 72
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.
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.