Run time error with multiple conditional format

[jrking]

I use this code so that I can have more than the standard 3 conditional formats, however, when I delete a row I get run time error. Is there any way to avoid the run time error?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("K:K")) Is Nothing Then
Select Case Target
Case "Zita Powell/Christa Collins"
 
Target.EntireRow.Select
Selection.Interior.ColorIndex = 8
icolor = 8
 
Case "Bo Hinnant"
Target.EntireRow.Select
Selection.Interior.ColorIndex = 43
icolor = 43
Case "Quint/Ray"
Target.EntireRow.Select
Selection.Interior.ColorIndex = 6
icolor = 6
Case "Xavier"
Target.EntireRow.Select
Selection.Interior.ColorIndex = 15
icolor = 15
 
Case ""
Target.EntireRow.Select
Selection.Interior.ColorIndex = 2
icolor = 2
 
 
End Select
 
Target.Interior.ColorIndex = icolor
End If
End Sub

Thanks

John

 

[crimson_b1ade]

perhaps try this modification. as an fyi you don't need to SELECT items to work with them in vba. Also I wasn't sure why you were changing the row color inside the select case statement then again outside of the select case statement so I changed the above code to do it only once (on the outside):

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim icolor As Integer
 
If Target.Column = 11 Then
 
    Select Case Target
 
    Case "Zita Powell/Christa Collins"
    icolor = 8
 
    Case "Bo Hinnant"
    icolor = 43
 
    Case "Quint/Ray"
    icolor = 6
 
    Case "Xavier"
    icolor = 15
 
    Case ""
    icolor = 2
 
    End Select
 
    Target.EntireRow.Interior.ColorIndex = icolor
 
End If
 
End Sub

 

[crimson_b1ade]

also, consider changing icolor = 0 for Case = "" (as opposed to '2'). Setting the color to '0' will clear all background color, which is what I'm assuming you wanted to do.

 

[jrking]

It works great, Thanks