AutoRun Macro Based on a Sum Calculation

I have recorded a few macros in Excel but I have very little expirence with
Visual Basic. I have written the following macro and it works great except
I would like to automatically run when the sum of "af1" gets below 10.

Sub Macro1()

Dim MyData As DataObject
Dim strClip As Integer

If Range("af1").Value > 9 Then
Exit Sub
Else
Range("af1").Select
Selection.Copy
Range("A4:C29,D1:I9,J1:AC3").Select

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText

Selection.Replace What:=strClip, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("Af1,a1:c3").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("af1).ClearContents

End If

End Sub

Place this code directly in the worksheet (right click the sheet tab and
select view code).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Target.Dependents, Range("A1F"))
On Error GoTo 0
If Not rng Is Nothing And _
Range("AF1") < 10 Then MsgBox "Call Macro 1"
End Sub
--
HTH...

Jim Thomlinson


"Jared J" wrote:

> I have recorded a few macros in Excel but I have very little expirence with
> Visual Basic. I have written the following macro and it works great except
> I would like to automatically run when the sum of "af1" gets below 10.
>
> Sub Macro1()
>
> Dim MyData As DataObject
> Dim strClip As Integer
>
> If Range("af1").Value > 9 Then
> Exit Sub
> Else
> Range("af1").Select
> Selection.Copy
> Range("A4:C29,D1:I9,J1:AC3").Select
>
> Set MyData = New DataObject
> MyData.GetFromClipboard
> strClip = MyData.GetText
>
> Selection.Replace What:=strClip, Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> Range("Af1,a1:c3").Select
> With Selection.Interior
> .ColorIndex = 6
> .Pattern = xlSolid
> End With
> Range("af1).ClearContents
>
> End If
>
> End Sub
>
>
>

Sorry you should turn off the events while the code is running like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

on error goto ErrorHandler
application.enableevents = false

On Error Resume Next
Set rng = Intersect(Target.Dependents, Range("A1F"))
On Error GoTo 0
If Not rng Is Nothing And _
Range("AF1") < 10 Then MsgBox "Call Macro 1"
ErrorHandler:
application.enableevents = true
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Place this code directly in the worksheet (right click the sheet tab and
> select view code).
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rng As Range
>
> On Error Resume Next
> Set rng = Intersect(Target.Dependents, Range("A1F"))
> On Error GoTo 0
> If Not rng Is Nothing And _
> Range("AF1") < 10 Then MsgBox "Call Macro 1"
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Jared J" wrote:
>
> > I have recorded a few macros in Excel but I have very little expirence with
> > Visual Basic. I have written the following macro and it works great except
> > I would like to automatically run when the sum of "af1" gets below 10.
> >
> > Sub Macro1()
> >
> > Dim MyData As DataObject
> > Dim strClip As Integer
> >
> > If Range("af1").Value > 9 Then
> > Exit Sub
> > Else
> > Range("af1").Select
> > Selection.Copy
> > Range("A4:C29,D1:I9,J1:AC3").Select
> >
> > Set MyData = New DataObject
> > MyData.GetFromClipboard
> > strClip = MyData.GetText
> >
> > Selection.Replace What:=strClip, Replacement:="", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> > ReplaceFormat:=False
> > Range("Af1,a1:c3").Select
> > With Selection.Interior
> > .ColorIndex = 6
> > .Pattern = xlSolid
> > End With
> > Range("af1).ClearContents
> >
> > End If
> >
> > End Sub
> >
> >
> >

Thanks Jim for the reply, but it does not seem to work like I envisioned it.
I have a sum formula in af1 and as cells in the range get deleted the sum in
af1 will decrease to a number between 1 & 9 and as soon as that happens I
would like excel to run Macro 1 automatcally. Once Macro 1 runs, it will
delete the sum formula in af1 so it does not continously run. I have looked
through alot of posts on this group and others but I can not seem to find
someone with the same issue. Maybe I am asking too much of excel.