name target.address to a range variable

bluesboytoo · October 15th, 2009, 03:35 AM

I WANT TO WORK OFF THE TARGET.ADDRESS FROM A WORKSHEET_CHANGE EVENT. SOMETHING LIKE: TARGET.ADDRESS.OFFSET(0,4) = MYRANGE....MY INTENTION IS TO USE THIS VARIABLE AS THE CELL ADDRESS IN A FEW CUSTOM VALIDATION FORMULAS.
I HAVE COLUMN "B" AS THE TARGET >
IT CONTAINS A DROP DOWN LIST WITH (8) CHOICES>
DEPENDING ON THE CHOICE(TARGET.VALUE )THE CUSTOM VALIDATION FORMULA WILL CHANGE IN COLUMN "F" OF THE SAME ROW.
THE SHORT OF IT IS THE VALIDATION FORMULAS CELL ADDRESS'S MUST NOT BE STATIC.
THANKS

romperstomper · October 16th, 2009, 10:48 AM

Target is itself a Range object, so just remove the .Address part.

bluesboytoo · October 18th, 2009, 07:32 PM

Thanks, But it didnt like that either. I know when I extend my code to place the target.address into a cell it will :$b$1 for exp. and it changes relative to the any of the target's cell's activation. But it won't let me use in anything else.
Thanks Again
Steve

romperstomper · October 19th, 2009, 02:34 AM

Hard to be specific without knowing what you actually tried or how it failed, but Target is a range object, so if you need to offset, you have to offset from Target, not from its address property:

Code:

target.offset(,4).address

for example.