Translate this code to worksheet_calculate listener

[breakbacktennis]

Hi, Im looking to translate this code into the worksheet_calculate listener as advised by members, however my lack of vba knowledge is getting the way. Is there anyway that can rewrite this code so it works in the calculate listener. Thankyou in advance!!

[COLOR=#333333][COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR][/COLOR][COLOR=#333333] 
   Dim KeyCells As Range[/COLOR]



   If Target.Address <> Range("G1").Address Then Exit Sub
    


        If Range("G1").Value = "In-play" Then
               Range("U9").Value = Range("G9").Value
               Range("U11").Value = Range("G11").Value
        End If
 [COLOR=#333333][COLOR=#333333]End Sub[/COLOR][/COLOR]

 

[JackDanIce]

Try:

Private Sub Worksheet_Calculate()

    If Range("G1").Value = "In-Play" Then
        Range("U9").Value = Range("G9").Value
        Range("U11").Value = Range("G11").Value
    End If
    
End Sub

 

[MrTeeny]

I'm guessing this is for some betting app, is there any reason to switch to calculate instead of change? Calculate is looking for a calculation to fire off the event so unless you have formulas on the sheet you won't fire it. To avoid Worksheet_change triggering itself we normally just turn off events whilst the code runs, to freeze your off prices just amend it to

Private Sub Worksheet_Change(ByVal Target As Range) 
On Error Goto xit
   Dim KeyCells As Range

   If Target.Address <> Range("G1").Address Then Exit Sub
    Application.EnableEvents = False



        If Range("G1").Value = "In-play" And Range("U9").Value =""  Then

         Range("U9").Value = Range("G9").Value
         Range("U11").Value = Range("G11").Value

        End If

xit:
 Application.EnableEvents = True
 End Sub

 

[breakbacktennis]

Hi MrTeeny. Your code works when I manually type in In-play however does not work when linked with the betting app which is constantly refreshing. This is why calculate may need to be used.

 

[MrTeeny]

No problem , the way you've coded it is that it will only fire once the value in G3 changes, once that has been set as In-play it won't detect a change so will exit the sub. With calculate your code will fire on every calculation of the sheet.