I'd like to run a macro based on a cell value being higher than another cell value

[sharmon]

Hi,

So i have two cells E27 & E28. Both cells are numeric.

E27 has a formula that changes based on some other cells inputs.

If E27 has a higher value than E28 i'd like to run a macro.

Else nothing should happen.

I'm sure this should be simple but i just can't get it to work.

Any help would be great! thanks!

 

[steve the fish]

Hi. Right click your sheet tab. Press view code. Insert this. Change macro name to suit.

Private Sub Worksheet_Calculate()

If Range("E27").Value > Range("E28").Value Then
    Call MyMacro
End If

End Sub

 

[sharmon]

It works fine! Thank you so much!

 

[sharmon]

Opps... one more thing...
I have 8 different macros which i want to run, so i need multiple instances of the code you gave me but referring to different cells i.e.

Run Macro1 if E27 is greater than E28
Run Macro2 if F27 is greater than F28
Run Macro3 if G27 is greater than G28
Run Macro4 if H27 is greater than H28
Run Macro5 if I27 is greater than I28
Run Macro6 if J27 is greater than J28
Run Macro7 if K27 is greater than K28
Run Macro8 if L27 is greater than L28

I tried copying the entire code and changing the cell references, but that's obviously not the right way and doesn't work...

 

[sharmon]

Sorry, i got it:

Private Sub Worksheet_Calculate()

If Range("E27").Value > Range("E28").Value Then
Call Macro1

End If
If Range("F27").Value > Range("F28").Value Then
Call Macro2

End If
End Sub

 

[sharmon]

Hi again, the above code works fine except if i am on a different sheet other than the one the code is put in, then if the macro gets triggered excel crashes. The cell which is triggered keeps flashing as though the macro doesn't stop triggering, but on a different sheet. Is there some extra code i need to put in to prevent this?

 

[steve the fish]

Can you post all the code you are using? So the worksheet_calculate code and the code of the macro you want to trigger.

 

[sharmon]

This is the code on the sheet:

Private Sub Worksheet_Calculate()

If Range("E27").Value > Range("E28").Value Then
Call Macro1

End If
If Range("F27").Value > Range("F28").Value Then
Call Macro2

End If
If Range("G27").Value > Range("G28").Value Then
Call Macro3

End If

If Range("H27").Value > Range("H28").Value Then
Call Macro4

End If
If Range("I27").Value > Range("I28").Value Then
Call Macro5

End If
If Range("J27").Value > Range("J28").Value Then
Call Macro6

End If
If Range("K27").Value > Range("K28").Value Then
Call Macro7

End If
If Range("L27").Value > Range("L28").Value Then
Call Macro8

End If

End Sub



And this is the code of one of the macros (the rest are the same with only cell references changed), which basically copies the E27 cell and pastes value into E28:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("E27").Select
Selection.Copy
Range("E28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[steve the fish]

Couldn't you do all this with formula in the workbook?

 

[sharmon]

Couldn't you do all this with formula in the workbook?

There is a way? All i need to do really is hold the highest value of a cell that keeps changing, is there a formula for that?