Uncolour past dates and colour only the current one-CodePudding

I have created a VBA code which displays with a blue colour the entire column where the today's date is located. The problem I have now is that my code does not uncolour the past dates. That means the colour of the past dates and the today's date is the same. I want only one column to be coloured which is the column of the today's date. I do not want for the past dates to have the same colour with the today's date. Current code below:

Any ideas?

Private Sub Workbook_Open()
    Dim CellToShow As Range
    Worksheets("Sheet2").Select
    x = Day(Date)
    Set CellToShow = Worksheets("Sheet2").Rows(3).Find(What:=x, LookIn:=xlValues) 'my dates are located across row 3
    CellToShow.EntireColumn.Interior.Color = RGB(151, 228, 255) 'background colour in the selected cell
    If CellToShow Is Nothing Then
        MsgBox "No Cell for day " & x & " found.", vbCritical
    Else
        With CellToShow
            .Select
            .Show 'Scroll the window to show the cell
        End With
    End If
End Sub

EDIT:

Here is the image of the problem.As you can see, Excel does put a colour in the column that contain the today's date. But also already colours the yesterday's date. I do not want yesterday's date been coloured. https://i.stack.imgur.com/oBf9P.png

CodePudding user response：

Use Conditional formatting - set 'Applies To' to be the whole worksheet $1:$1048576, and the formula to be =A$3=DAY(TODAY()). All the vba has to do is the message box if no relevant date is found, and scroll to the relevant column.
EDIT:
The reason this will work is because conditional formatting formulas are written for the first (top-left) cell in the range to be formatted (so for the whole sheet, this formula is written how it would apply to cell A1. The formula is then applied to other cells in the range in the same relative way - hence the use of A$3, the $ fixes it to always refer to row 3, while the A is left to change dynamically for each cell to be coloured. In this way the formula applies to whole columns, because the row reference is fixed.