Macro to Clear Cell Contents based on Cell Value in another Sheet

[CH]

Sorry for the Newbie question but.......

How do I create a macro to clear the contents of cells in one worksheet
based on the value of a cell in another worksheet?

For example: on SHEET 1 the value of cell A1 can be Yes or No. If A1 =
"Yes" then I want to clear the values in cell range A1:C5 of SHEET 2,
if "No" then don't clear the values.

Thanks in advance.

 

[Dave Peterson]

Option Explicit
sub testme()
dim rng1 as range
dim rng2 as range

set rng1 = worksheets("sheet1").range("a1")
set rng2 = worksheets("sheet2").range("a1:c5")

if lcase(rng1.value) = "yes" then
rng2.clearcontents
end if
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Option Compare Text
Sub clear_stuff()
If Sheets("Sheet1").Range("A1").Value = "Yes" Then
Sheets("Sheet2").Range("A1:C5").ClearContents
End If
End Sub


Gord Dibben MS Excel MVP

 

[CH]

Thanks that worked.

Is there a way to "undo" the clear when toggling back-and-forth between
"Yes" and "No". For example if the cell = "Yes" then clear the cells,
if cell = "No" then place original values back into the cell?

Thanks for the website too.

 

[Dave Peterson]

If you store those values somewhere, you could put them back.

But I would guess that it's kind of scary. If the user changes any of those
cells, you may be overwriting stuff that they want with older values.