Selected Answer
Hello Gurbir and welcome to the Forum
Not sure why you're not setting the formula in C3 to "=C2" but your code has two successive lines where you firstly set the variable Holder to a value (Holder = Target.Value) then have a test to see if it is that value (If Holder <> Target.Value Then...) so the code that follows will never run.
If you just want to store C2's value in C3 (withiout using the formula), try this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
Range("C3").Value = Target.Value
End Sub
The test in bold (above) means nothing happens unless it was C2 that changed.
If however your code declared the variable Holder for some other purpose (e.g. the new value to pass to other procedures) then you could either simply refer back to C2 (e.g. using Sheet1.Range("C2").Value) or a little more code will be needed.
In the code below, the variable is declared as a Public variable (so has "scope" outside the Worksheet_Change code) and this is illustrated by passing the varaible to another subroutine TestHolder which will print the changed value in VB Explorer's Intermediate window:
Public Holder As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
Holder = Target.Value
Range("C3").Value = Target.Value
TestHolder
End Sub
Private Sub TestHolder()
Debug.Print Holder
End Sub
Notice that the
Public declaration and
TestHolder subs are outside the
Worksheet_Change code. Clearly you would change the name and function of
TestHolder to do something useful!
Also I suggest you consider whether Holder needs to be delcared as a Variant (if you know what type of data is stored in C2 -especially if it's limited by Data Validation- then Long or String types might be better choices)
Hope this fixes your problem. Please let me know.
