VBA Conditional Statement That Functions Based on Today's Date

[default_name]

Hey everyone!

I am currently trying to implement a function into a workbook that has many worksheets.
Each worksheet has individual dates stored in cells E1:R1.

• When the workbook is opened I want the code to immediately run (so I put it in the Workbook_Open sub).
• The code looks through each worksheet.
  • If today's date is found on one of the worksheets (in E1:R1), then I would like it to then check for the following conditions:
    • If today's date appears between E1:K1 of that sheet, then I would like to hide column U
    • If today's date appears between L1:R1 of that sheet, then I would like to hide column T
  • If today's date is not found in E1:R1 of a worksheet, then I would like to hide both columns T and U and move on to the next sheet.

I am thinking that this might be done using an If/ElseIf/Else statement, but I am sure that I am overthinking it.
I have tried to play around with it and I feel like I am getting nowhere. Coding really isn't a strong trait I possess (as you will see below).

My garbage attempt

Private Sub Workbook_Open()
Dim currentDate As Date
currentDate = Date

For Each ws In Worksheets
With ws

   If Date = ws.Range("E1:K1").Find(Date, , , xlWhole, , , , , False)
   Then
      Columns("T:T").Select
      Selection.EntireColumn.Hidden = False
      Columns("U:U").Select
      Selection.EntireColumn.Hidden = True
   ElseIf Date = ws.Range("L1:R1").Find(Date, , , xlWhole, , , , , False)
      Columns("T:T").Select
      Selection.EntireColumn.Hidden = True
      Columns("U:U").Select
      Selection.EntireColumn.Hidden = False
   Else
      Columns("T:T").Select
      Selection.EntireColumn.Hidden = True
      Columns("U:U").Select
      Selection.EntireColumn.Hidden = True
   End If

End With
Next ws
End Sub

I hope that my request makes sense.
If not, please let me know and I can try to clarify.

 

[default_name]

Are your date cells appear left- aligned or right-aligned?

They are centered in the cell with the standard 'Date' formatting.

 

[Cubist]

They are centered in the cell with the standard 'Date' formatting.

What does it return when you do =ISNUMBER(date cell)?

 

[default_name]

What does it return when you do =ISNUMBER(date cell)?

Returns: TRUE

 

[Cubist]

I'm not sure why it's returning type mismatch when it's a date. Try replace all instances of
DateValue(cell.Value) with just cell.Value

 

[default_name]

I'm not sure why it's returning type mismatch when it's a date. Try replace all instances of
DateValue(cell.Value) with just cell.Value

That worked! I pulled the Not at the end as well, and it works perfectly!

Here is the final code (in case anyone else may find this helpful)

Private Sub Workbook_Open()
Dim currentDate As Date
Dim ws As Worksheet
Dim hideT As Boolean
Dim hideU As Boolean
Dim cell As Range
Dim dateFoundInE As Boolean
Dim dateFoundInL As Boolean

currentDate = Date

For Each ws In Worksheets
hideT = True
hideU = True
dateFoundInE = False
dateFoundInL = False

For Each cell In ws.Range("E1:K1")
If cell.Value = currentDate Then
dateFoundInE = True
Exit For
End If
Next cell
For Each cell In ws.Range("L1:R1")
If cell.Value = currentDate Then
dateFoundInL = True
Exit For
End If
Next cell

hideT = dateFoundInL
hideU = dateFoundInE
ws.Columns("T:T").Hidden = hideT
ws.Columns("U:U").Hidden = hideU
Next ws
End Sub

Thanks Cubist! I really appreciate your help!

 

[default_name]

Oh dang, I closed and reopened the file and the code doesnt quite work. There are no errors, but there is still something missing.
The latter portion seems to work fine (the sheets that do not contain today's date have both columns T and U hidden)

• ✓ When the workbook is opened I want the code to immediately run (so I put it in the Workbook_Open sub).
• The code looks through each worksheet.
  • If today's date is found on one of the worksheets (in E1:R1), then I would like it to then check for the following conditions:
    • If today's date appears between E1:K1 of that sheet, then I would like to hide column U
    • If today's date appears between L1:R1 of that sheet, then I would like to hide column T
  • ✓ If today's date is not found in E1:R1 of a worksheet, then I would like to hide both columns T and U and move on to the next sheet.

However, the sheet that DOES contain today's date is not hiding any of the columns. Both columns T and U are visible.
Hmmm...thoughts?

 

[Cubist]

I've tested multiple variations. This seems to work.

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim todayDate As Date
    Dim dateRange As Range
    Dim foundDateCell As Range
    todayDate = Date

    For Each ws In ThisWorkbook.Worksheets
        ws.Columns("T:U").Hidden = False
        Set dateRange = ws.Range("E1:R1")
        Set foundDateCell = dateRange.Find(What:=todayDate, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundDateCell Is Nothing Then
            If Not Intersect(foundDateCell, ws.Range("E1:K1")) Is Nothing Then
                ws.Columns("U").Hidden = True
            End If
            If Not Intersect(foundDateCell, ws.Range("L1:R1")) Is Nothing Then
                ws.Columns("T").Hidden = True
            End If
        Else
            ws.Columns("T:U").Hidden = True
        End If
    Next ws
End Sub

 

[default_name]

I've tested multiple variations. This seems to work.

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim todayDate As Date
    Dim dateRange As Range
    Dim foundDateCell As Range
    todayDate = Date

    For Each ws In ThisWorkbook.Worksheets
        ws.Columns("T:U").Hidden = False
        Set dateRange = ws.Range("E1:R1")
        Set foundDateCell = dateRange.Find(What:=todayDate, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundDateCell Is Nothing Then
            If Not Intersect(foundDateCell, ws.Range("E1:K1")) Is Nothing Then
                ws.Columns("U").Hidden = True
            End If
            If Not Intersect(foundDateCell, ws.Range("L1:R1")) Is Nothing Then
                ws.Columns("T").Hidden = True
            End If
        Else
            ws.Columns("T:U").Hidden = True
        End If
    Next ws
End Sub

That's the one! Thank you for your patience on this!! I really appreciate it!

 

[Cubist]

You're welcome. Glad that worked out. Cheers.