VBA to lock column values

[Hugo]

Hi,

I would like to be able to use a subroutine to lock cells in columns that
have column headers of specific dates. In this sittuation, I have quarterly
dates interjected between monthly dates in the cells in the row that serves
as the column headers. For the columns that have a header of a monthly date,
say "Jul-2009", I would like the cells in a certain range of that column say
row 8 through row 11 to be unlocked, and for a columns that have a header of
a quarterly date, say "Q2-2009", I would like the cells in that range (rows
8-11) to be locked.

Any thoughts?

Thanks!

 

[Dave Peterson]

If those "date" values are simply text, you may be able to use something like:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myHeader As Range

With Worksheets("sheet1")
.unprotect password:="topsecret"

Set myHeader = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))

For Each myCell In myHeader.Cells
If LCase(myCell.Value) Like "q#-####" Then
Intersect(myCell.EntireColumn, .Rows(8).Resize(4)).Locked = True
ElseIf LCase(myCell.Value) Like "???-####" Then
Intersect(myCell.EntireColumn, .Rows(8).Resize(4)).Locked = False
End If
Next myCell

.protect password:="topsecret"
End With
End Sub