Private Sub Worksheet_Change(ByVal Target As Range) problem

[zingomax]

Hi

I am trying to run the code below which is based on a signal to change to a new market once the current one has closed and one other condition based on the time to off being positive still.
. It is not working and I think it is because the
Private Sub Worksheet_Change(ByVal Target As Range) part is looking for change in the 16 columns of data before running which of course has stopped because the market is closed

Is there another way to make the code run regularly without the 16 column update requirements?

_______________
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

If Range("F36").Value = 2 Then
Range("Q2").Value = -1
Else: Range("Q2").Value = ""

Application.EnableEvents = True
End Sub

[negapo]

I'm not using BA so i don't know what signals a market being close but you can check for that:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 and Range("Q2").Value <> "Closed" Then Exit Sub
Application.EnableEvents = False

If Range("F36").Value = 2 Then
Range("Q2").Value = -1
Else: Range("Q2").Value = ""

Application.EnableEvents = True
End Sub

This is just an example, don't know the cell and the info on the cell to signal a market is close.

[Captain Sensible]

The market closed status is sent to F2 so I just check if that says Closed and move on if it is