jchris7854
New Member
- Joined
- Apr 17, 2013
- Messages
- 2
Greetings Excel Guru's
I am having an issue that i cannot seem to rectify. I first want to say that if it was not for this website then i would not even know how to use VBA so i am very gracious that you guys are out there to help... On to my issue
I am having to use more than 3 conditional formats for a worksheet i am working on with drop down responses, this is for a Project Reporting Spreadsheet that is ever updating.
For example: Status is my dropdown that consist of 5 different responses all formatted to change color based on the value
Status: Not Started (light blue), In Process (Green), Delay (Yellow), Past Due(Red) and Complete (Grey)
I have this drop down on more than 2 columns ( Column I(9) and Column K(11)) and this is the code i currently have in place....
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 11 Then
With Target.Interior
Select Case UCase(Target.Value)
Case "NOT STARTED"
.ColorIndex = 33
Case "IN PROCESS"
.ColorIndex = 43
Case "DELAY"
.ColorIndex = 6
Case "PAST DUE"
.ColorIndex = 3
Case "COMPLETE"
.ColorIndex = 16
End Select
End With
End If
If Target.Column <> 9 Then
With Target.Interior
Select Case UCase(Target.Value)
Case "NOT STARTED"
.ColorIndex = 33
Case "IN PROCESS"
.ColorIndex = 43
Case "DELAY"
.ColorIndex = 6
Case "PAST DUE"
.ColorIndex = 3
Case "COMPLETE"
.ColorIndex = 16
End Select
End With
End If
End Sub
When i make i try to delete or cut and paste i encounter the "Runtime Error 13 Type Mismatch" error message every time, my though is that it has to do with me changing more than one cell in the column the code references ( I & K)
I am hopeful there is a quick fix to my issue
Thanks in advance for any assistance,
John
I am having an issue that i cannot seem to rectify. I first want to say that if it was not for this website then i would not even know how to use VBA so i am very gracious that you guys are out there to help... On to my issue
I am having to use more than 3 conditional formats for a worksheet i am working on with drop down responses, this is for a Project Reporting Spreadsheet that is ever updating.
For example: Status is my dropdown that consist of 5 different responses all formatted to change color based on the value
Status: Not Started (light blue), In Process (Green), Delay (Yellow), Past Due(Red) and Complete (Grey)
I have this drop down on more than 2 columns ( Column I(9) and Column K(11)) and this is the code i currently have in place....
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 11 Then
With Target.Interior
Select Case UCase(Target.Value)
Case "NOT STARTED"
.ColorIndex = 33
Case "IN PROCESS"
.ColorIndex = 43
Case "DELAY"
.ColorIndex = 6
Case "PAST DUE"
.ColorIndex = 3
Case "COMPLETE"
.ColorIndex = 16
End Select
End With
End If
If Target.Column <> 9 Then
With Target.Interior
Select Case UCase(Target.Value)
Case "NOT STARTED"
.ColorIndex = 33
Case "IN PROCESS"
.ColorIndex = 43
Case "DELAY"
.ColorIndex = 6
Case "PAST DUE"
.ColorIndex = 3
Case "COMPLETE"
.ColorIndex = 16
End Select
End With
End If
End Sub
When i make i try to delete or cut and paste i encounter the "Runtime Error 13 Type Mismatch" error message every time, my though is that it has to do with me changing more than one cell in the column the code references ( I & K)
I am hopeful there is a quick fix to my issue
Thanks in advance for any assistance,
John