Please help, I'm working on a project to design a new forecast template and I
need to be able to lock a range of cells dependent on a given month. For
instance

A1 = Target Month

Range K9 : V9 has the 12 months of the year where K9 = Jan 2010, L9 = Feb
2010 ... V9 = Dec 2010

As the months in the year move along, I'd like to lock all prior months for
the corresponding cell months for rows 18-251. So if the Target Month (A1)
is April 2010, then I'd like to lock cells K9:M251. In essence I'd like to
restrict users from unintentionally changing actual data and only input their
new forecast for the upcoming months.

I took a couple of stabs at it and tried the following code, but was still
missing something. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If K9 <= A1 Then
Range("K11:K251").Locked = True
End If
End Sub

No one ever responded, but fortunately I was able to go through various
related discussion posts and piece together enough VBA code to accomplish
what I wanted. So in an effort to save anyone else the headache and trouble
here goes what I used. There may be more efficient ways to accomplish this,
but hopefully someone else will find this useful.

With Sheets("SuntrustPlan Template")
.Unprotect Password:="MyPass"
.Range(Cells(7, "k"), Cells(281, "v")).Interior.ColorIndex = 0
Cells.Locked = False

'November 2009
If Cells(8, "l").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "k")).Locked = True
.Range(Cells(7, "k"), Cells(281, "k")).Interior.ColorIndex = 0
.Range(Cells(7, "l"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'December 2009
ElseIf Cells(8, "m").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "l")).Locked = True
.Range(Cells(7, "k"), Cells(281, "l")).Interior.ColorIndex = 0
.Range(Cells(7, "m"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'January 2010
ElseIf Cells(8, "n").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "m")).Locked = True
.Range(Cells(7, "k"), Cells(281, "m")).Interior.ColorIndex = 0
.Range(Cells(7, "n"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'February 2010
ElseIf Cells(8, "o").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "n")).Locked = True
.Range(Cells(7, "k"), Cells(281, "n")).Interior.ColorIndex = 0
.Range(Cells(7, "o"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'March 2010
ElseIf Cells(8, "p").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "o")).Locked = True
.Range(Cells(7, "k"), Cells(281, "o")).Interior.ColorIndex = 0
.Range(Cells(7, "p"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'April 2010
ElseIf Cells(8, "q").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "p")).Locked = True
.Range(Cells(7, "k"), Cells(281, "p")).Interior.ColorIndex = 0
.Range(Cells(7, "q"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'May 2010
ElseIf Cells(8, "r").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "q")).Locked = True
.Range(Cells(7, "k"), Cells(281, "q")).Interior.ColorIndex = 0
.Range(Cells(7, "r"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'June 2010
ElseIf Cells(8, "s").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "r")).Locked = True
.Range(Cells(7, "k"), Cells(281, "r")).Interior.ColorIndex = 0
.Range(Cells(7, "s"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'July 2010
ElseIf Cells(8, "t").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "s")).Locked = True
.Range(Cells(7, "k"), Cells(281, "s")).Interior.ColorIndex = 0
.Range(Cells(7, "t"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'August 2010
ElseIf Cells(8, "u").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "t")).Locked = True
.Range(Cells(7, "k"), Cells(281, "t")).Interior.ColorIndex = 0
.Range(Cells(7, "u"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
'September 2010
ElseIf Cells(8, "v").Value = Cells(4, "a").Value Then
.Range(Cells(7, "k"), Cells(281, "u")).Locked = True
.Range(Cells(7, "k"), Cells(281, "u")).Interior.ColorIndex = 0
.Range(Cells(7, "v"), Cells(281, "v")).Interior.ColorIndex = 32
.Protect Password:="MyPass"
End If
End With
End Sub

"SK8" wrote:

Please help, I'm working on a project to design a new forecast template and I
need to be able to lock a range of cells dependent on a given month. For
instance

A1 = Target Month

Range K9 : V9 has the 12 months of the year where K9 = Jan 2010, L9 = Feb
2010 ... V9 = Dec 2010

As the months in the year move along, I'd like to lock all prior months for
the corresponding cell months for rows 18-251. So if the Target Month (A1)
is April 2010, then I'd like to lock cells K9:M251. In essence I'd like to
restrict users from unintentionally changing actual data and only input their
new forecast for the upcoming months.

I took a couple of stabs at it and tried the following code, but was still
missing something. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If K9 <= A1 Then
Range("K11:K251").Locked = True
End If
End Sub