Excel VBA changing greater than to less than gives Run-time Error 1004

Question

So I posted the following question yesterday:Link

and was able to write the following code to (kinda) solve it:

Sub LockDateCols()

Dim j As Range

Sheets("Sheet1").Unprotect
curdate = Int(CDbl(Now()))

For Each j In Sheets("Sheet1").Range("F6:As6").Cells
    If curdate > j.Value Then
        j.EntireColumn.Locked = False
    End If
Next j
Sheets("Sheet1").Protect
End Sub

This code locks the columns that have a date value GREATER than the current date and it needs to do the opposite but when I switch the greater than sign i get a Runtime 1004 error that says "unable to set the locked property of the range class"

I'm basically out of ideas, there are no merged ranges.

`curdate > j.Value` states that the date in `j` will be less than the current date? `j.value>now` will be greater and `j.value — Nathan_Sav, Feb 12 '19 at 14:46
Right, but the problem is it works as written, but when i flip it to get the results I want I get the runtime error — DBA108642, Feb 12 '19 at 14:49
Can you show some data????? Flipping the sign shouldn't give you an issue. Have you done any debugging yourself? — Nathan_Sav, Feb 12 '19 at 14:54
I have the sample data in the original question. I've tried debugging on my own, it says the error is on the line `j.EntireColumn.Locked = False`. Im really not sure what is going on — DBA108642, Feb 12 '19 at 15:10
So it's not your code then, is the worksheet still protected? — Nathan_Sav, Feb 12 '19 at 15:11
No need for else really, just add this in your if block `Sheets("Sheet1").Protect` — Nathan_Sav, Feb 12 '19 at 15:25

score 0 · Accepted Answer · answered Feb 12 '19 at 15:25

The following code works:

Sub LockDateCols()

Dim j As Range

Sheets("Proposed Baseline").Unprotect
curdate = Int(CDbl(Now()))

For Each j In Sheets("Proposed Baseline").Range("f6:As6").Cells
    If j.Value > curdate Then
        j.EntireColumn.Locked = False
    Else
        j.EntireColumn.Locked = True
    End If
Next j
Sheets("Proposed Baseline").Protect
End Sub

Instead of just trying to do it with one line I added an else statement and that seems to have done the trick.

Excel VBA changing greater than to less than gives Run-time Error 1004

1 Answers1