macro to run IF condition met

  • Thread starter Thread starter Savuti
  • Start date Start date
S

Savuti

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 > 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
 
if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
 
Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub
 
Sub TEST()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If

End Sub
 
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan
 
You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick
 
Rick thank you. I learned something new - much appreciated -Lord I have a lot
to learn
 
That's why you're the MVP! :) Thanks!

Rick Rothstein (MVP - VB) said:
You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick
Click to expand...
 
Alan, thank you as well for taking the time to respond. The more ways of
learning to do something the better.
 
Back
Top