Foutmelding macro

[chiellebeest]

Hallo,

ik gebruik onderstaande code om cellen in een sheet te kleuren, so far so good! Echter als ik de cellen wil leegmaken, (alle cellen selecteren, delete) krijg ik een foutmelding. Weet iemand hier een oplossing voor?

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   With Target
   
    Select Case .Value
      Case Is = vbNullString
          .Interior.ColorIndex = xlNone
      Case Is = "a1", "a2", "a3", "a4", "a5"
          .Interior.ColorIndex = 15
          
      Case Is = "b1", "b2", "b3", "b4", "b5"
          .Interior.ColorIndex = 6
          
      Case Is = "c1", "c2", "c3", "c4", "c5"
          .Interior.ColorIndex = 37
          
      Case Is = "d1", "d2", "d3", "d4", "d5"
          .Interior.ColorIndex = 40
          
      Case Is = "e1", "e2", "e3", "e4", "e5"
          .Interior.ColorIndex = 43
          
      Case Is = "f1", "f2", "f3", "f4", "f5"
          .Interior.ColorIndex = 22
          
      Case Is = "mp"
         .Interior.ColorIndex = 39
                 
      Case Else
          .Interior.ColorIndex = xlNone
    End Select

   End With
   

  End Sub

Fout 13 tijdens uitvoering:
Typen komen niet met elkaar overeen

bij foutenopsporing krijg ik de foutmelding op:
Case Is = vbNullString

Michiel

 

[VenA]

Probeer het zo eens.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
   Application.EnableEvents = False
   If Target.Count = 1 Then
        Select Case .Value
        Case Is = vbNullString
          .Interior.ColorIndex = xlNone
        Case Is = "a1", "a2", "a3", "a4", "a5"
          .Interior.ColorIndex = 15
          
        Case Is = "b1", "b2", "b3", "b4", "b5"
          .Interior.ColorIndex = 6
          
        Case Is = "c1", "c2", "c3", "c4", "c5"
          .Interior.ColorIndex = 37
          
        Case Is = "d1", "d2", "d3", "d4", "d5"
          .Interior.ColorIndex = 40
          
        Case Is = "e1", "e2", "e3", "e4", "e5"
          .Interior.ColorIndex = 43
          
        Case Is = "f1", "f2", "f3", "f4", "f5"
          .Interior.ColorIndex = 22
          
        Case Is = "mp"
         .Interior.ColorIndex = 39
                 
        Case Else
          .Interior.ColorIndex = xlNone
        End Select
        Else: .Interior.ColorIndex = xlNone
    End If
  Application.EnableEvents = True
End With
End Sub

 

[snb]

Of gewoon met

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   target.interior.colorindex=xlnone
   if target<>"" and instr("abcdefm",left(lcase(target),1)) then target.interior.colorindex=choose(instr("abcdefm",left(lcase(target),1)),15,6,37,40,43,22,39)
End Sub

 

[chiellebeest]

Beide bedankt! Het werkt :thumb: