Looking for macro that will autofill cells in a range

[dmcnab]

Hi all...I am looking for a macro that will auto-fill cells in a range. The attached worksheet lists dates ("Closed") in column A....I want to enter more dates (eg: holidays) when the office is closed. As I do that, the combined effect of formulas in col N and col D cause the word "Closed" to be entered in col D.


Columns B,C,D are protected; range E8:M88 must remain unprotected and without any formula in those cells. I am looking for some type of macro or 'change sheet' event that will cause "Closed" to be entered in a row (eg: e13:m13) if the date in c13 is entered in col A, (or if it is easier, if the value in d13 = "Closed")......and I can't use conditional formatting to simply shade the row, b/c I have used all 3 cond formatting rules for that range already......any suggestions? ...see attachment below


http://www.speedyshare.com/files/28609611/Autoclose.xls

 

[Hui]

Can you just put a simple formula in

E13: =IF(OR(LEN($A13),$D13="Closed"),"Closed","")

Copy across and down

 

[dmcnab]

Hi Hui.....can't do that b/c the range e8:mm88 has to be formula-free so that I can enter data into it....

 

[Luke M]

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub


If Target.Value = Cells(Target.Row, "C") Then

Application.EnableEvents = False

Range(Cells(Target.Row, "E"), Cells(Target.Row, "M")).Value = "Closed"

End If

Application.EnableEvents = True


End Sub