Row dropdown triggers converting entire table row to values

I have a table called “TABLE1” with the fist column being "Status" which is a dropdown of 3 possible values ("ACTIVE", "PENDING" and "SOLD").

If I select "SOLD" from the dropdown I want THAT SAME SPECIFIC TABLE ROW to convert to values. Meaning all formulas or functions within that row turn to values (so they never change).

Maybe the drop-down selection of "SOLD" would trigger a macro that would copy and PASTE as “Values & Number Formatting”. Not sure exactly how to do this.

I need help knowing the VBA code to enable for this please.

Please use prop syntax for tables (not ranges).

see attached file

Try:

Please Login or Register  to view this content.

Artik

Thank you . It seems to work except it changes the number formatting. (It is changing "Accounting" number format to "Currency" number format.

What if the dropdown column was the 2nd column? Would I then update your syntax to:

(Target, LO.Range.Columns(2))

In fact, Excel arbitrarily converts the accounting format to currency (ah that smug Excel!). Because of this and a different monitored column than the first, the code gets a bit complicated.

Please Login or Register  to view this content.

Artik

it works! Thank you Artik!