Macro to pick up item from list

Hi Friends,

I have 14 columns in my excel. In that the first 13 columns i have made validation to list items like Pending,completed,Awaiting approval,Awaiting confirmation.

For the 14th column i have named it as "Status". In that also i have made validation to list items like Pending, completed,Awaiting approval and Awaiting confirmation.

Now i need a macro coding so that if i select in any one of the 13 columns as pending then the status column should automaticallly get changed to Pending.

And if i all the column reads as completed then the status column should automatically change into completed.

Any one can help on this.

Thanks in advance.

Right-click on the sheet tab and VIEW CODE to put in this sheet-event macro:

Please Login or Register  to view this content.

Once you close the vbeditor and save, this will operate in realtime and update the column 14 status anytime you put "pending" in the first 13 columns.



REMINDER/WARNING: Once you start using sheet-event macros, you may find your UNDO option works less often.

Corrected above, make that the plural.

StephenR above had the right idea for that.

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

The code is correct.

My file does exactly what you've asked. The code does not need nor care if you've also put data validation in column N, it's not using data validation, it's writing "Pending" and "Completed" directly into the cell.

The code is correct. If you're having difficulty in your sheet, then post it.

It's not working because you changed the code to use the COUNTIF() = 22 instead of COUNTIF() = 13 like I had it originally. There are only 13 columns from J:W, not 22, so the total still needs to be13 to trigger the "completed" action.

Please Login or Register  to view this content.