Hi there,
I need some help with VBA macro’s in a spreadsheet I have. I have attached the Spreadsheet and this as explanation of what I want it to do:
1. In sheet2 cell E5 is a countdown timer in seconds which is formula derived from the countdown in Cell F3
2. In sheet2 cell H1 displays the word ‘Suspended’ when the timer reaches 0 seconds or sometimes in the minuses but not until then.
3. In L9,L11,L13 etc. (right down to L67 but only every second cell) I have formulas.
4. Cells M9, M11, M13 etc. (right down to M67 but only every second cell) also have formulas.
5. Cells N9, N11, N13 etc. (right down to N67 but only every second cell) also have formulas.
6. Cells 09, 011, 012 etc. (right down to O67 but only every second cell) displays a status as text when H1 displays the word ‘Suspended’
7. Once the word ‘Suspended’ is displayed in H1 I need a macro that clears all of the cells on L9, L11, L13 etc (right down to L67 but only every second cell), and also Cells O9, O11, O13 etc (right down to O67 but only every second cell). I think the below code does this? (might need someone with experience to check it)
Private Sub Worksheet_Change(ByVal Target As Range)
If [login to view URL] = "$C$2:$C$6" Then
If Range("H1") = "Suspended" Then
' clear global betting command
Range("L6:N6").ClearContents
' clear betting command
Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47,L49,L51,L53,L55,L57,L59,L61,L63,L65,L67").ClearContents
' clear status
Range("O6,O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").ClearContents
End If
End If
End Sub
Here is my problem however: Once these cells are cleared I need to re-populate Cells L9, L11 ,L13 etc and down with all of the original formulas as they disappear after being cleared by the first macro above. I want this to happen when the value in E5 is >=46 and <=50.
I hope this all makes sense?
Cheers,
Sam