How can I trick excel into thinking cell data has changed?

I have a macro that runs when a cell data changes and it works fine, however I want to have a button on the first sheet that will run this macro on every single page, i dont want to rewrite the whole crazy long macro from multiple pages into a button module, so i was thinking its easier if i just make excel think the data changed so it would run the macro to update it.

Here is what I have, but it seems to not work for whatever reason:

Sub FullUpdateTest()

 'Clear all traffic lights
        Sheets("Geräte, Einrichtungen..").Activate
        Range("G5:M300").Select
        Selection.ClearContents
        Sheets("Geräte, Einrichtungen..").Activate
        Range("E55:E300").Select
        Selection.ClearContents
        Sheets("Kalkulat. ALV").Activate
        Range("E4:E300").Select
        Selection.ClearContents
        Sheets("Dokumente").Activate
        Range("E4:E300").Select
        Selection.ClearContents
        Sheets("Instandhaltungskosten").Activate
        Range("E4:E300").Select
        Selection.ClearContents



StartRow = 2
EndRow = 300


For RowCnt = StartRow To EndRow

SheetToActivate = "Geräte, Einrichtungen.."
Sheets(SheetToActivate).Activate

TempInfo = Cells(RowCnt, 2)
Range(Cells(RowCnt, "2")).Select
Selection.ClearConents
Cells(RowCnt, 2) = "74893740141"
Cells(RowCnt, 2) = TempInfo

SheetToActivate = "Kalkulat. ALV"
Sheets(SheetToActivate).Activate

TempInfo = Cells(RowCnt, 2)
Range(Cells(RowCnt, "2")).Select
Selection.ClearConents
Cells(RowCnt, 2) = "74893740141"
Cells(RowCnt, 2) = TempInfo

SheetToActivate = "Dokumente"
Sheets(SheetToActivate).Activate

TempInfo = Cells(RowCnt, 2)
Range(Cells(RowCnt, "2")).Select
Selection.ClearConents
Cells(RowCnt, 2) = "74893740141"
Cells(RowCnt, 2) = TempInfo

SheetToActivate = "Instandhaltungskosten"
Sheets(SheetToActivate).Activate

TempInfo = Cells(RowCnt, 2)
Range(Cells(RowCnt, "2")).Select
Selection.ClearConents
Cells(RowCnt, 2) = "74893740141"
Cells(RowCnt, 2) = TempInfo



Next RowCnt



End Sub

This is the code i have on 3 of the 4 tabs (the code on the 4th is a lot longer and more complicated, and it seems to be the same issue, just giving as example).

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

    Dim KeyCells As Range
    Set KeyCells = Range("B5:B300")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then

        'Variables
        TodaysDate = Date
        LastUpdateDate = ActiveSheet.Cells(Target.Row, Target.Column)
        Interval = "0"
        Interval = ActiveSheet.Cells(Target.Row, Target.Column + 1)
        NextUpdateDate = DateAdd("d", Interval, LastUpdateDate)
        YellowDate = DateAdd("d", -14, NextUpdateDate)
    
    'Red
    If TodaysDate >= NextUpdateDate Then
        ActiveSheet.Cells(Target.Row, Target.Column + 3) = "1"
    End If
    
    'Green
    If (TodaysDate < YellowDate) Then
        ActiveSheet.Cells(Target.Row, Target.Column + 3) = "3"
    End If
    
     'Yellow
    If (TodaysDate > YellowDate) And (TodaysDate < NextUpdateDate) Then
        ActiveSheet.Cells(Target.Row, Target.Column + 3) = "2"
    End If
    
     If ActiveSheet.Cells(Target.Row, Target.Column).Value = "" Then
        ActiveSheet.Cells(Target.Row, Target.Column + 3) = ""
    End If

End If

End Sub

Thanks Josie,

It was running before, but this seems simpler anyway. It runs ok, but now the problem is, if you look at the other set of code I posted, it only seems to run the segment that ends up setting the one column to red/yellow 1, 2. For whatever reason it never will set the cells to 3 when I use the button, but when I activate it by changing the date normally, it works fine, i see no reason why this would happen.

Originally Posted by JosephP

Range(Cells(RowCnt, "2")).Select
Selection.ClearConents

should not compile or run-it oughta be

Cells(RowCnt, 2).Select
Selection.ClearContents

or simply

Cells(RowCnt, 2).ClearContents

Magically all I did was change startrow in the module macro from 2, to 3, and it all works perfectly now... no idea why this would do anything signifigant like that.

wrong attachmenet