VBA Code: Change event, multiple if's for a range

[dhopman]

I'm brand new to excel VBA - found some code that works to change the value of one cell based on a single if statement. I'm trying to write something that will have two if statements, and apply to a range.

Column C contains one of two values - "Base" or "Option"...let's assume there's 10 rows so C1:C10
Cell A2 will have a pick list for "On" or "Off" - this will be the change event that applies to any of the Options in Column C.

In D1:D10, I want "Off" to be returned if A2="Off" and the value next to it in column C is "Option".
If A1 = "On", all the options should return "On" in column D2

I hope that's clear enough...I have a feeling this will be very simple for someone out there. A file is attached for some visual assistance. Thanks!!!

[protonLeah]

Why VBA? Use =IF(AND(A$2="Off",C1="Option"),"Off","On") in C1 and copy down.

[dhopman]

Should have guessed that question was coming. I don't want to use a formula because I want the user to be able to turn on/off individual elements if they choose. If I use a change event via VBA, the user can pick and choose, or just turn all the options on/off in one swipe.

[dhopman]

BUMP - anyone have any ideas on how to solve this one yet? Pretty please?

[JapanDave]

Hi,

protonLeah obviously provides a great solution, but if it is a macro you are after.

Try this.

[dhopman]

THANK YOU JapanDave! That worked perfectly. You rock.