Case Statement on Changed Cell's Value

Hello all,

While perusing the interwebs, I found the following sub below. The idea is that when a cell is changed in the worksheet that it runs a case statement against what the changed cell's new value is. It seems pretty straight forward but when I run it in debug mode the Set Rng1 = Range(Target.Address) is making Rng1 = the target's VALUE and not the ADDRESS. So if I change V16 to J then Rng1 = J and the Case statement then apparently cycles through every cell in column J until it errors out. What I don't understand is that when I hover over Range(Target.Address) it shows Target.Address is $V$16 which is correct... So why is it setting Rng1 = "J" (the value)??

If Rng1 should equal "J" (what I change the cell to) and not "V16" (the cell that was changed), what should I use so that the Switch statement only cycles through the 1 changed cell instead of a whole column?

Please advise.

Please Login or Register  to view this content.

How are you passing in Target?

If you are doing it by cell reference then the ByVal will pass the value and not the reference.

That makes sense - d'oh! It's being passed ByVal as seen in the code.

I perused the internet some more and found a different variation and was able to get that to work.

If anyone else finds this via search, here's the code I ended up using:

Please Login or Register  to view this content.