Copy and Paste Validation List Error

[Yogin]

Hi,

I am getting the following error - "Run-time error '13': Type Mismatch" when
I copy and paste a range of cells from validation list to another range
within the same worksheet. I am using the following code for conditional
formatting, could this be the cause of the problem?
----------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G7:GT700")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True

With Target
Select Case .Value
'Days
Case "DHOL"
..Cells.Interior.ColorIndex = 7
Case "DHOL4"
..Cells.Interior.ColorIndex = 7
Case "DS"
..Cells.Interior.ColorIndex = 8
Case "DRD"
..Cells.Interior.ColorIndex = 4
Case "DEX"
..Cells.Interior.ColorIndex = 3
Case Else
..Cells.Interior.ColorIndex = xlNone
End Select
End With
End If
End Sub

Thanks

Yogin

 

[Mike H]

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G7:GT700")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
For Each c In Target
Select Case c.Value
'Days
Case "DHOL"
c.Interior.ColorIndex = 7
Case "DHOL4"
c.Interior.ColorIndex = 7
Case "DS"
c.Interior.ColorIndex = 8
Case "DRD"
c.Interior.ColorIndex = 4
Case "DEX"
c.Interior.ColorIndex = 3
Case Else
c.Interior.ColorIndex = xlNone
End Select
Next c
End If
End Sub

Mike

 

[Yogin]

Thanks Mike,

This works really well.

Yogin

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G7:GT700")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
For Each c In Target
Select Case c.Value
'Days
Case "DHOL"
c.Interior.ColorIndex = 7
Case "DHOL4"
c.Interior.ColorIndex = 7
Case "DS"
c.Interior.ColorIndex = 8
Case "DRD"
c.Interior.ColorIndex = 4
Case "DEX"
c.Interior.ColorIndex = 3
Case Else
c.Interior.ColorIndex = xlNone
End Select
Next c
End If
End Sub

Mike

 

[Mike H]

Glad I could help

Thanks Mike,

This works really well.

Yogin