Copy and Paste Validation List Error

Y

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
 
M

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
 
Y

Yogin

Thanks Mike,

This works really well.

Yogin

Mike H said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MACRO HELP 9
Interior color disappears 1
please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Conditional formating 2
Out of Stack Space error 2
Private sub worksheet_change(bval target as range) issue 2
How do I make some columns all caps and others proper? 2
2 'myStr' values in one Worksheet, need VB Code help! 3

Top