Multiple Conditional Formatting VBA Runtime Error

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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi and welcome to the forum

Try this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Target.Column <> 11 Or 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
            Case Else
                .ColorIndex = xlNone
        End Select
    End With
End If
End Sub

I added a Case Else to clear formatting if you blank off a cell for any reason.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top