Clear a range of cells if a value is posted in another cell

[steve64]

Hi to All,
As a frustrated VBA "Virgin" I would be so grateful if one of you gurus could help me out.
Bearing in mind I am a real beginner (but very enthusiastic) could you walk me though the steps I need to do to achieve this hopefully straightforward task.

I have a cell in my worksheet (called Sheet 1), in the sheet I have a basic Excel formula that will change cell T40 that usually reads "0" to a "1" if the criteria I want is met (in effect a trigger cell(?))]

So.......

When T40 is displaying a "1" I want to clear cells T5:T38

My newbie questions is once I have this code, where exactly should I put it.

I'd be really grateful for any help. Thanks in advance
Steve

[vcoolio]

Hello Steve,

A basic code such as follows would do the task for you:-

Please Login or Register  to view this content.

To implement the code, go to the Developer tab and over to the far left, select Visual Basic. This will open the VB Editor (you can also use Alt + F11 to open it). Once open, select Insert. From the menu that appears, select Module. The big grey field to the right will change to white. In this field, paste the above code. Go back to your spread sheet, press Alt + F8 and the Macro dialogue box will appear. Click on "Run" to execute the code. You could, of course, create a button to assign the macro to and run the code by clicking on the button.

Every time that cell T40 has a 1 in it, the code will execute otherwise, nothing will happen.

I hope that this helps.

Cheerio,
vcoolio.

[steve64]

Thanks very much for your help vcoolio.
It works a treat, except I need it to run all the time. I didn't explain very well.
The sheet is hooked up to an api that is constantly changing the figures. So what I need it to do is change whenever a "1" appears.
When I applied this code and followed your instructions, it cleared the cells, the next time the "1" appeared nothing happened.
It's probably my mistake somewhere.
Cheers
Steve

[vcoolio]

Hello Steve,

When I applied this code and followed your instructions, it cleared the cells, the next time the "1" appeared nothing happened.

You actually have to run the code again.

However, a Worksheet_Change event may be a better option considering your updated explanation.

Make the following change to the code in post #2:-

Please Login or Register  to view this content.

Now, right click on the sheet tab and select view code. In the big white field that appears, paste the following:-

Please Login or Register  to view this content.

You now don't need to do anything. Every time the value in T40 changes to "1", the range T5:T38 will clear.

Cheerio,
vcoolio.

[steve64]

Perfect!!!
Really appreciate the time you have taken to help me vcoolio
Cheers
Steve

[vcoolio]

Hello Steve,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.