Making a macro run on a "paste special-values"

CO Engr

New Member
Joined
Jan 2, 2004
Messages
6
I have a worksheet that receives a "paste special - values" whenever text is entered into another sheet. On this receiving sheet, I have a macro that automatically turns a cell a certain color when the proper text is entered. When I type the text, or paste one cell, the macro works fine. But if I 'paste special - values' for a whole column the macro doesn't run. If I then click in a cell with the specified text, the cell then changes color. Is there a way to modify this macro so that the user doesn't have to click in each cell to get the background color to change?

Thanks for your help, here is the code that I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Set isect = Application.Intersect(Range("A1:CC1000"), Range(Target.Address))
If Not isect Is Nothing Then
ColorCode = Right(Trim(Target.Value), 3)
Select Case ColorCode
Case "CAR": Target.Interior.ColorIndex = 40
Case "CA": Target.Interior.ColorIndex = 34
Case "CD": Target.Interior.ColorIndex = 35
Case "DD": Target.Interior.ColorIndex = 37
Case "SD": Target.Interior.ColorIndex = 38
Case "CON": Target.Interior.ColorIndex = 39
Case Else
Target.Interior.ColorIndex = 0
End Select
End If
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,237
CO Engr said:
When I type the text, or paste one cell, the macro works fine. But if I 'paste special - values' for a whole column the macro doesn't run.
Well, why do you have this line in there then?
If Target.Count > 1 Then Exit Sub

Take it out and see what happens when you paste an entire column.

Also, make sure the activity taking place, which you expect to result in a triggerable event, is indeed taking place within A1:CC1000.
 

CO Engr

New Member
Joined
Jan 2, 2004
Messages
6
I took that line out and then pasted an entire column and I got an error message "Run time error 13 - Type mismatch" How should I code this so that I can change more than one cell and still have the routine run?

Thanks for your help.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,237
If you take a look at your code, you'll notice that the crux of the color index change is based on one cell changing. That is fine if you are changing one cell at a time. But, if you change your activity plans, by pasting in an entire column, then you need to adjust your code to loop through the new cells that have been changed, so they are evaluated and colored accordingly. The structure of the procedure as it is now served your first intention of changing one cell at a time, but it needs to be rewritten to identify the range being changed, and then loop through the individual cell objects to shade them. This would be a good candidate for conditional formatting if all you are transferring is values, but you have a half-dozen colors to deal with so VBA seems like the only alternative.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,770
Members
416,879
Latest member
Excel_Newbie4980

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top