Help please - delete cells/columns dependent on another cell value

[dwilson38550m]

Can anyone help at all....if cell a1 (sheet1) =5 I want to delete cells g5 to k25 however if a1 (sheet1) =6 of want to delete cells h5 to k25. Cell a1 will change - if a1 =7 then I want to delete i5 to k25. Is there any way I can do this?

Thanks in advance

 

[offthelip]

put this in the wroksheet change event for worksheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range(Cells(1, 1), Cells(1, 1))) Is Nothing) Then
 nn = Target.Value
  If nn > 4 And nn < 8 Then
   Range(Cells(5, 2 + nn), Cells(25, 11)) = ""
  End If
End If


End Sub

 

[dwilson38550m]

Sorry I was having problems getting this working - how do you mean by Worksheet Change Event? I didn't seem to work.

 

[offthelip]

To put code in the worksheet change event
From the excel window type ctrl F11, this will open the VBA project window
In the project explorer window on the left of the screen , right click the “sheet1” object and select “viewCode"
A new blank windows opens , in the left hand dropdown menu at the top, select “worksheet”
A bit of code appears in the blank window (ignore this)
In the right hand dropdown menu select “Change”
You should now have some code in the window that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

Delete the last two lines and edit the worksheet change sub to look like my post above