Running Macro when cell value changes

[Ranger73]

I have seen other posts like this but their solutions don't work for me. I have 2 excel sheets. The first sheet is updated based on a button in powerapps. When the button is pressed, it runs a microsoft flow and adds a row to this data sheet. The second sheet is macro enabled (since flow doesnt work with sheets that are macro enabled) and it is set to equal the values on the first page basically copying the first sheet so that i can run macros on the data. I need a macro to run anytime the second sheet is updated with a row from the first sheet, and ive tried using the If Not Intersect(Target, Range("A4:A500")) Is Nothing Then line of code to do so, but for some reason it doesn't detect a change when the sheet is updated. Im thinking it is because the cells are based on a formula that copies data from the first sheet and since the formula doesnt change, the code doesnt detect a change when it is updated. Any help on this problem would be great, thank you

 

[Ranger73]

I have tried inserting this code into the sheet code
Private Sub Worksheet_Calculate()
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 4 To RowCount
Static OldVal1 As Variant
If Range("h" & i).Value <> OldVal1 Then
OldVal1 = Range("a" & i).Value
Call Compile_Dylan_Munson
End If
Next


RowCount = Cells(Cells.Rows.Count, "h").End(xlUp).Row
For j = 4 To RowCount
Static OldVal2 As Variant
If Range("h" & j).Value <> OldVal2 Then
OldVal2 = Range("h" & j).Value
Call Compile_Dylan_Munson
End If
Next
End Sub
and the macro Compile_Dylan_Munson that it is running is this
Sub Compile_Dylan_Munson()
'comipling clock in table
c
Rows("4:500").Delete
Name = Range("A1").Value
Sheets("Main_Time").Select
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 4 To RowCount
If (InStr(1, (Range("a" & i).Value), Name) > 0) Then
Range("a" & i, "f" & i).Copy
Sheets("Dylan_Munson").Select
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
ActiveSheet.Range("a" & RowCount + 1).PasteSpecial xlPasteValues
ActiveSheet.Range("a" & RowCount + 1).PasteSpecial xlPasteFormats
Sheets("Main_Time").Select
End If
Next
'compiling clock out table
Sheets("Main_Time").Select
RowCount = Cells(Cells.Rows.Count, "h").End(xlUp).Row
For i = 4 To RowCount
If (InStr(1, (Range("h" & i).Value), Name) > 0) Then
Range("h" & i, "m" & i).Copy
Sheets("Dylan_Munson").Select
RowCount = Cells(Cells.Rows.Count, "h").End(xlUp).Row
ActiveSheet.Range("h" & RowCount + 1).PasteSpecial xlPasteValues
ActiveSheet.Range("h" & RowCount + 1).PasteSpecial xlPasteFormats
Sheets("Main_Time").Select
End If
Next
Range("A1").Select
Sheets("Dylan_Munson").Select
End Sub

So when i update a row, it seems to work and it runs the macro, but i receive a "subscript out of range" error on the "Sheets("Dylan_Munson").Select" line in the macro. The Dylan_Munson page is in the same workbook as the main page running the macro so im not sure why it is out of range. Also if i manually run the macro it works and i dont get this error

 

[Ranger73]

That "c" in the macro code is supposed to say "Sheets(Dylan_Munson).Select" i think i accidentally replaced it with c when i was copying

 

[DanteAmor]

I have seen other posts like this but their solutions don't work for me. I have 2 excel sheets. The first sheet is updated based on a button in powerapps. When the button is pressed, it runs a microsoft flow and adds a row to this data sheet. The second sheet is macro enabled (since flow doesnt work with sheets that are macro enabled) and it is set to equal the values on the first page basically copying the first sheet so that i can run macros on the data. I need a macro to run anytime the second sheet is updated with a row from the first sheet, and ive tried using the If Not Intersect(Target, Range("A4:A500")) Is Nothing Then line of code to do so, but for some reason it doesn't detect a change when the sheet is updated. Im thinking it is because the cells are based on a formula that copies data from the first sheet and since the formula doesnt change, the code doesnt detect a change when it is updated. Any help on this problem would be great, thank you

It is correct, for the change event to activate, you must change the cell, if you have a formula what changes is the result of the formula, but the content of the cell is the same.

I help you to continue with your macro, but tell me what you need to do.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

In the sheets you explain step by step your need.