jdub12280
03-17-2010, 12:52 PM
Hello,
Im back for a slightly different twist on my last undertaking. You guys helped me tremendously before, so thought i'd lean on you once more. Long and short, I have a worksheet "DataSheet" that has an input range (c1:ag46). each cell in the first row of this range has a date (march 1 thru march 31). I am trying to use the attached code to Lock cells of any column in the above range, if the date at the top of the column is < or > than the current date. I had planned on using this in either a workbook open event, or after a custom login event. Stepping through, i am able to see that i have set the range correctly, and i am getting it to step column by column, however the column headers (.cells(1,1)) of each column which are to be compared with Date aren't being evaluated 1 at a time. When i used the msg box to show me what was being evaluated, column headers are stepping +2 instead of +1. For the life of me, i can't figure out why.
My goal is to have all cells in range c1:c46 locked if c1 < or > today (03/17). Then repeat for each column... thru AG.
Any assistance or suggestions are greatly appreciated... Please help me get back on track....
Justin
Private Sub test()
Const PW As String = "secretpw"
Dim mRange As Range
Dim cl As Range
Dim mArea As Range
Dim i As Variant
i = 1
Do Until i = 30
With Sheets("DataSheet")
.Unprotect PW
On Error GoTo exithandler
Set mRange = .Range(.Cells(1, 3), .Cells(46, 33)) '"c1", "ag46")
MsgBox mRange.Address '<--test to see if right range is set
For Each cl In mRange
' For i = 1 To 30 Step 1
Set mArea = mRange.Range(.Cells(1, i), .Cells(46, i))
'Set mArea = .Range(.Cells(1, i), .Cells(46, i)) '.MergeArea
'<<<<test to see if comparing to right date, and locking corresponding column>>>>
MsgBox "Range to lock - " & mArea.Address & "date to compare - " & mArea.Cells(1, i).Value 'Cells(1, i).Value
If mArea.Cells(1, i).Value < Date Then cl.Locked = True
i = i + 1
Next cl
exithandler:
.Protect PW
End With
End Sub
Im back for a slightly different twist on my last undertaking. You guys helped me tremendously before, so thought i'd lean on you once more. Long and short, I have a worksheet "DataSheet" that has an input range (c1:ag46). each cell in the first row of this range has a date (march 1 thru march 31). I am trying to use the attached code to Lock cells of any column in the above range, if the date at the top of the column is < or > than the current date. I had planned on using this in either a workbook open event, or after a custom login event. Stepping through, i am able to see that i have set the range correctly, and i am getting it to step column by column, however the column headers (.cells(1,1)) of each column which are to be compared with Date aren't being evaluated 1 at a time. When i used the msg box to show me what was being evaluated, column headers are stepping +2 instead of +1. For the life of me, i can't figure out why.
My goal is to have all cells in range c1:c46 locked if c1 < or > today (03/17). Then repeat for each column... thru AG.
Any assistance or suggestions are greatly appreciated... Please help me get back on track....
Justin
Private Sub test()
Const PW As String = "secretpw"
Dim mRange As Range
Dim cl As Range
Dim mArea As Range
Dim i As Variant
i = 1
Do Until i = 30
With Sheets("DataSheet")
.Unprotect PW
On Error GoTo exithandler
Set mRange = .Range(.Cells(1, 3), .Cells(46, 33)) '"c1", "ag46")
MsgBox mRange.Address '<--test to see if right range is set
For Each cl In mRange
' For i = 1 To 30 Step 1
Set mArea = mRange.Range(.Cells(1, i), .Cells(46, i))
'Set mArea = .Range(.Cells(1, i), .Cells(46, i)) '.MergeArea
'<<<<test to see if comparing to right date, and locking corresponding column>>>>
MsgBox "Range to lock - " & mArea.Address & "date to compare - " & mArea.Cells(1, i).Value 'Cells(1, i).Value
If mArea.Cells(1, i).Value < Date Then cl.Locked = True
i = i + 1
Next cl
exithandler:
.Protect PW
End With
End Sub