macro to run on entire range, not just changed cells

hi all

i have a macro running grand whenver i change a cell to a value within the range specified in the code.

what i want is for the macro to activate when the cell is open, scan every sheet for specified values, and call a 2nd macro for every corresponding value.

here is the code as is (after some help here yesterday!)

Option Explicit
Private Sub Worksheet_change(ByVal Target As Range)
   Dim buf As String
   Dim cell As Range

   If Not Intersect(Range("E5:CG100"), Target) Is Nothing Then
      For Each cell In Intersect(Range("E5:CG100"), Target)
         ' only want to process the odd numbered columns,
         ' E being column 5, G column 7 and so on
         If cell.Column Mod 2 = 1 Then
            If cell.Value = Date + 60 Then
               buf = buf & vbLf & cell.Address & " = " & cell.Value _
                     & " (training expires in 60 days - refresher required: " & Cells(4, cell.Column).Value _
                     & " / " & Cells(cell.Row, 1).Value & ")"
            ElseIf cell.Value = Date + 10 Then
               buf = buf & vbLf & cell.Address & " = " & cell.Value _
                     & " (training expires in 10 days - refresher urgently required: " & Cells(4, cell.Column).Value _
                     & " / " & Cells(cell.Row, 1).Value & ")"
            ElseIf cell.Value = Date Then
               buf = buf & vbLf & cell.Address & " = " & cell.Value _
                     & " (training expires today - refresher required immediately: " & Cells(4, cell.Column).Value _
                     & " / " & Cells(cell.Row, 1).Value & ")"
            ElseIf cell.Value < Date And cell.Value > 100 Then
               buf = buf & vbLf & cell.Address & " = " & cell.Value _
                     & " (training has expired, refresher urgently required: " & Cells(4, cell.Column).Value _
                     & " / " & Cells(cell.Row, 1).Value & ")"
            ElseIf cell.Value = "" Then
               buf = buf & vbLf & cell.Address & " = " & cell.Value _
                     & " (never been trained - training required: " & Cells(4, cell.Column).Value _
                     & " / " & Cells(cell.Row, 1).Value & ")"
            End If
         End If
      Next cell
      If buf <> "" Then Call Mail_small_Text_Outlook(buf)
   End If

End Sub
 
 
Sub Mail_small_Text_Outlook(sText As String)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    strbody = "Hi there" & vbNewLine & vbNewLine & "nightshift" & vbNewLine & vbNewLine & sText & ""
 
    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "training alert"
        .Body = strbody
        .Send
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

how do i go about that?

thanks