ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Copy and Paste Validation List Error (https://www.excelbanter.com/excel-discussion-misc-queries/198258-copy-paste-validation-list-error.html)

Yogin
Copy and Paste Validation List Error
 
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
Copy and Paste Validation List Error
 
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" wrote:

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

Yogin
Copy and Paste Validation List Error
 
Thanks Mike,

This works really well.

Yogin

"Mike H" wrote:

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" wrote:

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
Copy and Paste Validation List Error
 
Glad I could help

"Yogin" wrote:

Thanks Mike,

This works really well.

Yogin

"Mike H" wrote:

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" wrote:

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


All times are GMT +1. The time now is 10:37 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com