run macro on condition

[isildad]

In range A1:A5 if any cell value >0 then ran macro?
how to write this in VBA?

 

[isildad]

OK like that (more complicate):

If Application.CountA(Range("A1:A5")) = 0 Then
    Exit Sub
    Else
Call macro

 

[Smitty]

How are the values in that range changing? Via formula or manually? For the former you can use your existing logic in a Calculate event, for the latter you can use a Change event.


HTH,

 

[isildad]

Manually
too much code in present changeevent prefer to use as is above but need to have additional condition ex:

If Application.CountA(Range("A1:A5")) = 0 Then
    Exit Sub
'here add 2nd condition
  ' OR If ("B1").value = "cancel"
'Exit Sub

    Else
Call macro

thkx

 

[Smitty]

If Application.CountA(Range("A1:A5")) = 0 Or Range("B1").Value = "Cancel" Then...

 

[isildad]

correct code but not good for this situation (my bad) because if B1=cancell then code to delete sheet

maybe with "case" like this?
(do not know how to right case code)

case Application.CountA(Range("A1:A5")) = 0 
case exit sub

case Range("B1").Value = "Cancel"
case exit sub 
delete sheet

case Application.CountA(Range("A1:A5")) = >0 
case call macro

 

[Smitty]

I'm not sure what you're trying to do. I'd probably leave the = 0 test out, since all it looks like you're doing is Exiting the sub anyway, and focus on the other 2 conditions.

If Application.CountA(Range("A1:A5")) = >0 Then 
  Call Macro
If Range("B1").Value = "Cancel" Then 
  Call DeleteSheet
End if
End If

 

[isildad]

you are right. thkx for help