Pasting values from another worksheet doesn't activate the change in Visual Basic in excel to run wh

T

temp

Greetings All

I'm trying to get the color of the cell background to change when the value changes in the cell.
The code works but when I paste in data from another worksheet the code isn't executed. For some reason
it only works if I go into each cell seperatly and change it by hand.

How can I get the code to run and evaluate all cell data after the new data has been pasted in?
Atl -F11 ctrl-shift-f11 doesn't work either

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As Integer

If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:Z44")

If Not Intersect(Target, WatchRange) Is Nothing Then

Select Case CellVal
Case 4, -8, 7, -5, 1, -2
Target.Interior.ColorIndex = 6
Case -4, 8, -7, 5, -1, 2
Target.Interior.ColorIndex = 26
Case 3, -3, -6, 6,9,-9
Target.Interior.ColorIndex = 2


'Case (IsBlank)
'Target.Interior.ColorIndex = 19
'Case 41 To 50
'Target.Interior.ColorIndex = 45
End Select
End If
End Sub

tia sal2
 
M

Michael

To debug your macro select the whole set of instructions and copy it to a new
module, do not copy the following piece:
Private Sub Worksheet_Change(ByVal Target As Range)
Instead, type
Sub Test()
Then hit F8 to step it through and you will find out why is not running
properly.
 
J

JLGWhiz

The code worked for me. I copied from sheet to to sheet one changing from 7
to 8 and the color changed to blue. Maybe what you are copying and pasting
is not numeric??? or does not match one of your case criteria.
 

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

Top