Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Copy and Paste Validation List Error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 17
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 17
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect Data validation cells from copy paste Guneet Ahuja Excel Discussion (Misc queries) 3 May 21st 08 04:07 PM
problem data validation and copy/paste Theo Excel Worksheet Functions 2 January 13th 08 08:30 PM
Data Validation - Copypaste special problem WCM Excel Discussion (Misc queries) 2 October 24th 06 02:13 AM
Copy/Paste over rides data validation jk Setting up and Configuration of Excel 1 July 23rd 06 03:39 AM
Data validation does not seem to work with copy/paste LAF Excel Discussion (Misc queries) 1 September 15th 05 09:31 PM


All times are GMT +1. The time now is 04:39 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement