Offset from active cell until given range

[KyleJackMorrison]

Hey,

So here is my code and i require it to offset select cells to the left until J23 and to the right until NO23.

Sub hideme()


Sheets("Settings").Visible = True
Sheets("Main").Select
For Each cell In ActiveSheet.Range("J23:NO23")
    If cell.Value = [Today()] Then
    cell.Select
    End If
Next
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, [U]NO23[/U])).EntireColumn.Hidden = True
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, [U]J23[/U])).EntireColumn.Hidden = True

'do something
End Sub

In theory, if the cell X23 contained today's date. All columns to the right get hidden until column 'NO' and all columns to the left get hidden until J23.

 

[Fluff]

How about

Sub hideme()
Dim Cell As Range

Sheets("Settings").Visible = True
Sheets("Main").Select
For Each Cell In ActiveSheet.Range("J23:NO23")
    If Cell.Value = [Today()] Then
    Select Case Cell.Column
      Case 10
         Range("K:NO").EntireColumn.Hidden = True
      Case 379
         Range("J:NN").EntireColumn.Hidden = True
      Case Else
         Range("J23", Cells(23, Cell.Column - 1)).EntireColumn.Hidden = True
         Range(Cells(23, Cell.Column + 1), Range("NO23")).EntireColumn.Hidden = True
      End Select
    End If
    Exit Sub
Next
End Sub

 

[KyleJackMorrison]

As far as me testing this it didn't work. Nothing changed

Just a question, as the code works for Today(), can this then be adapted as easily as Today()+1, Today()+2.. etc?

 

[Fluff]

What do you mean "Nothing changed"?
Do you have today's date somewhere in the range J23:NO23?

 

[Norie]

Why do you have this?

Exit Sub

That will exit the loop after the first iteration, i.e. after the value in J23 has been checked.

If it should be anywhere that line of code should be within the If..End If.

 

[KyleJackMorrison]

So inputting that code and running it made no change to the sheet. No columns hidden.
And not even an activecell selected just the previous activecell prior to running the code.

So today's date is actually in AS23 formatted dd/mm/yy.

 

[Fluff]

Good spot Norie
The code should be

Sub hideme()
Dim Cell As Range

Sheets("Settings").Visible = True
Sheets("Main").Select
For Each Cell In ActiveSheet.Range("J23:NO23")
    If Cell.Value = [Today()] Then
      Select Case Cell.Column
        Case 10
           Range("K:NO").EntireColumn.Hidden = True
        Case 379
           Range("J:NN").EntireColumn.Hidden = True
        Case Else
           Range("J23", Cells(23, Cell.Column - 1)).EntireColumn.Hidden = True
           Range(Cells(23, Cell.Column + 1), Range("NO23")).EntireColumn.Hidden = True
      End Select
      Exit Sub
    End If
Next Cell
End Sub

This assumes that the date only appears once in the row.

 

[KyleJackMorrison]

Awesome thank you! I can confirm that this is exactly what i needed. big up the Fluff and Norie.

 

[Fluff]

Glad we could help & thanks for the feedback