PDA

View Full Version : CASE Statement type mismatch



impius
03-04-2009, 01:01 PM
Hello, I got the following code for conditional formatting but I get an error every time I delete a row. The code works, until I go to delete the row. The error is "type mismatch" and it highlights the first case statement...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Boo1 As Boolean
Dim intclr As Integer
If Not Intersect(Target, Range("A1:z100")) Is Nothing Then
Select Case Target
Case "yes"
intclr = 6
Case 6
intclr = 12
Case 26 To 30
intclr = 42
Case Else
End Select
Target.Interior.ColorIndex = intclr
End If
End Sub

Thanks for any help.

Simon Lloyd
03-04-2009, 01:44 PM
Try changing this lineIf Not Intersect(Target, Range("A1:z100")) Is Nothing Then for thisIf Not Intersect(Target, Range("A1:z" & Range("Z" & rows.count).end(xlup).row)) Is Nothing Then

mdmackillop
03-04-2009, 01:45 PM
Your code can only test one cell at a time so
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:z100")) Is Nothing Then