Macro run depending on cell value

Yes, this should be straightforward. I take it you want the relevant procedure to be called when M3 is changed? If yes, then does M3 contain a formula (it would change when the worksheet is calculated) or a constant (it would change when someone types in a new value)?

Hi,

Right click on the sheet's tab > View code.

Copy and paste in this procedure:

Private Sub Worksheet_Calculate()
    
    Dim rngToCheck As Range
    
    Set rngToCheck = Range("M3")
    
    If VBA.IsNumeric(rngToCheck.Value) Then
        
        If rngToCheck.Value > 10 Then
            Macro3
            
        ElseIf rngToCheck.Value >= 5 Then
            Macro4
            
        End If
    End If
End Sub

Hi Colin thanks for that. Something isn't quite right though as the macro it calls for seems to freeze, or get stuck (it continually loops the first part by the looks of things). Is there a conflict somewhere?

My macro 3 code is as follows

Range("E37").Select
    Selection.Copy
    Range("E31").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C37").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C31").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B20:B29").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D20:D29").Select
    Selection.ClearContents
    Range("B7:E29").Select
    With Selection.Interior
        .Pattern = xlGray50
        .PatternThemeColor = xlThemeColorLight1
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

The actions you are performing in that macro are causing the calculate event to be raised: your code is calling itself recursively. To get around this you have to temporarily set Application.EnableEvents to False - but you must ensure it gets set back to True before the procedure ends.

I've tidied up the code and incorporated this for you:

Sub Macro3()
 
    On Error GoTo ErrorHandler
 
    Application.EnableEvents = False
 
    Range("E31").Value = Range("E37").Value
    Range("C31").Value = Range("C37").Value
    Range("B20:B29").ClearContents
    Range("D20:D29").ClearContents
    With Range("B7:E29").Interior
        .Pattern = xlGray50
        .PatternThemeColor = xlThemeColorLight1
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
ErrorExit:
    Application.EnableEvents = True
    Exit Sub
 
ErrorHandler:
    Resume ErrorExit
 
End Sub

You may well find that you have to perform a similar exercise with Macro4.