Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add worksheet change event to add a pre specified number looking up the corresponding row value


I have attached a sample excel file where when a user enters a text say from B3 to B5  Expert, Intermediate or beginner, it should lookup colum M and enter a number in the 'No' column using worksheet change function.

Can you please help me do it?



Selected Answer

Hi Dr. Liss,

Updated July 28, 4PM - Worksheet_Change event method (.....modified_V2)

Looking at your file I chose to use an IF formula. This is a simpler approach than a Worksheet_Change event. Using the IF formula requires the "No" columns (C, G, E, etc.) to be protected so the formula doesn't become corrupted or deleted.

In Col J I added your criteria 2 conditional formatting. I also resized some columns for appearance.

If you prefer the Worksheet_Change event method let us know and I'm confident one of us will provide that solution for you.


OK, so I had some free time so I modified the file again so that it uses the Worksheet_Change event to enter the value. File is attached. (.....modified_V2)




Thank you so much
Dr Liss (rep: 26) Jul 29, '22 at 11:09 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login