Selecting a range of cells based on a loop variable

Question

I've tried for days to find an answer to this but honestly, it might be a lack of knowledge in the subject that's causing me to not search for the right terms.

I have a spreadsheet with a series of dates between S7:GE7 and rows from 8:96 that have data that need to be locked the next day.

I know my way of selecting the columns is all wrong, and there's probably more wrong too but I can't think of a way to make it right.

Private Sub Workbook_Open()

Dim i As Range, cell As Range
Set i = Range("S7:GE7")

For Each cell In i
      If (cell.Value < DateValue(Now())) Then
          Range(i + "8:96").Locked = True
      End If
Next cell

End Sub

What I'm hoping to achieve with this is a loop that looks through the dates in S7:GE7, and if the date is older than today it locks cells 8:96 in that column.

Any help that can be provided would be much appreciated. Thanks

SuShuang · Accepted Answer · 2017-06-05 10:31:17Z

1

Try this:

Private Sub Workbook_Open()

Dim i As Range, cell As Range
Set i = Range("S7:GE7")

For Each cell In i
      If (cell.Value < DateValue(Now())) Then

          cell.Offset(1, 0).Resize(79, 1).Locked = True

        End If
Next cell

End Sub

answered Jun 5, 2017 at 10:31

SuShuang

1906 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting a range of cells based on a loop variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related