you may have to manually intervene there. typically, i'd use a module level variable to identify the current market, then compare that to the market in Bet Angel sheet B1. If this is different, then clear out the cells (i.e. in code). I in fact have a little clear up routine that i use specifically for this purpose. This routine gets called in the Bet_Angel Worksheet_Change event:
Code: Select all
Private Sub ClearBetStatuses()
Dim intRow As Integer, wsSource As Worksheet
Const BET_NOTIFICATION_COL = 12
Const BET_STATUS_COL = 15
Const BET_STATUS_ROW_START = 9
Const BET_STATUS_ROW_END = 60
Set wsSource = ThisWorkbook.Sheets("Bet Angel")
wsSource.Range("L6:O6") = ""
For intRow = BET_STATUS_ROW_START To BET_STATUS_ROW_END Step 2
wsSource.Cells(intRow + 1, BET_NOTIFICATION_COL) = ""
wsSource.Cells(intRow, BET_STATUS_COL) = ""
Next
End Sub