Type Mismatch Error when Inserting a new row
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 |
Type Mismatch Error when Inserting a new row
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) "BC" wrote in message ... 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 |
Type Mismatch Error when Inserting a new row
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 "Chip Pearson" wrote: 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) "BC" wrote in message ... 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 |
All times are GMT +1. The time now is 04:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com