macro to clear range of cells

[nhmaster]

Hello

Sure this request is pretty simple, can anyone create a VBA code macro that will clear cells T5, U5,V5,W5 if cells AB15 = 2, it needs to be running continuously, it will be clearing bet refs etc to enable additional triggers to fire. Be much appreciated if anyone can help or send me a private message

Cheers Shaun

[phrenetic]

Shaun

Try this (typed in directly so not tested!):

Code: Select all

Option Explicit

Dim wb1 As Workbook
Dim ws1 As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
  ' Only process whole updates
  If Target.Columns.Count <> 16 Then Exit Sub
 
  ' Set-up workbook variables
  Set wb1 = ThisWorkbook
  Set ws1 = wb1.Sheets("Sheet1")
 
  ' Stop any further updates until we have completed
  Application.EnableEvents = False
 
  ' Check for 2 in cell AB15
  If ws1.Range("AB15").Value = 2 Then
     ' Clear bet references
     ws1.Range("T5:W5").ClearContents
  End If
 
  ' Re-enable updates
  Application.EnableEvents = True
 End Sub


Alistair

[nhmaster]

Hello alistar

Thanks for that, just to check I do it right, if I copy and paste the code into where exactly. I have opened visual basic with the spreadsheet open and am presented with list of sheets 1/3 my worksheet is sheet 1 so I click that, I have 2 dropdown boxes at top of sheet what do I select GENERAL or WORKSHEET

Many thanks in advance

[Ian]

WORKSHEET

[phrenetic]

From the spreadsheet, choose the tab that you want, right click and select view code.

Paste the code into the empty area that opens up (on the right hand side of the list of sheets, etc).

Alistair