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