Type Mismatch Error when Inserting a new row

[Guest]

Hi,

I have written some conditional formatting code which changes the background
color of certain cells based on their value. This works perfect, until a user
tries to insert a new row into the table - when they do this they received a
Run Time Error '13' - Type Mismatch.

The code I am using is shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("Table")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub


Any help would be very much appreciated,

Thanks

BC

 

[Chip Pearson]

When you insert a row, Target refers to the EntireRow (address = $2:$2, for
example). You can't compare a multi-cell range to a number, so you get a
Type Mismatch error.

Use

Select Case Target(1, 1)

Of
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("Table")) Is Nothing Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Thanks for the help Chip that's great. Only issue now is that the lookup
formula that's applied to that column isn't coped through to the inserted
row. Would you have any ideas?

Thanks again

BC