Macro to run when cell value changes without hitting Enter key

[sparkling13]

Hello everyone,

I am new to programming and is very hopeful that someone can help me with a code to run the Macro that I already have.
I have a worksheet with several tabs, and one tab gets the value (via link) from another tab. So every time the value on the Tab1 changes, the value on cell "D9" changes on Tab2, but the macro on Tab2, won't run unless I hit enter key on cell "D9".

Can anyone please help me with a code that would run the code even without hitting the enter key.

Please and Thank you!

Sparkling13

[Kaper]

Not enough data, but most probably:
In code of Sheet Tab1 insert handler of worksheet change event, something like:

Please Login or Register  to view this content.

Of course instead of A1 use the the address of cell in Tab1 which leads to change in Tab2

And in YourMacro remember to give references to cells/ranges with worksheet name included
so something like this in standard module:

Please Login or Register  to view this content.

[sparkling13]

Thank you for your reply, Kaper.
I will try to incorporate that to my current code.

This is the code I have right now...


'Revision Level
If Not Application.Intersect(Target, rngD) Is Nothing Then
'Captures changes in far-right columns
If Cells(r, "I").Value2 = "" Then
Cells(r, "I").Value2 = Cells(r, "D").Value2

Else
newCol = Cells(r, Columns.Count).End(xlToLeft).Column + 1
'newCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column + 1
Columns("I").Copy
Columns(newCol).PasteSpecial xlPasteFormats
Cells(7, newCol).Value2 = "New Date - Received" & newCol - 9
Cells(r, newCol).Value2 = Cells(r, "D").Value2
If Cells(r, "D").Value2 <> "" Then
Cells(r, "D").Interior.Color = vbRed 'change color to red if changed
End If
End If
End If

'Approval Status Level
If Not Application.Intersect(Target, rngG) Is Nothing Then
'Captures changes in far-right columns
If Cells(r, "J").Value2 = "" Then
Cells(r, "J").Value2 = Cells(r, "G").Value2
Else
newCol = Cells(r, Columns.Count).End(xlToLeft).Column + 1
'newCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column + 1
Columns("J").Copy
Columns(newCol).PasteSpecial xlPasteFormats
Cells(7, newCol).Value2 = "New Date Approval Sent" & newCol - 10
Cells(r, newCol).Value2 = Cells(r, "G").Value2
If Cells(r, "G").Value2 <> "" Then
Cells(r, "G").Interior.Color = vbRed 'change color to red if changed
End If
End If
End If

[Kaper]

Where is the code (in which procedure is it in selectionChange or in Change event handler)?
And in which sheet code?

Please edit your post to comply with Rule 3 of the forum (using CODE tags).

[sparkling13]

It is in the Change event handler... When a date changes in Sheet 1, it should change in Sheet 3 as well, as this value is linked to Sheet 3.

So it should record in Sheet 3, if the date on Sheet 1 has been changed.

Thank you.