Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
keep getting type mismatch error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |