Today only

[GIDRA]

I have a code that when I open the file marks the date in a calendar by a
highlighted cell (coloured). The problem is that when the day after I open
the file again I find that yesterday's date cell is still highlighted,
include today's date cell.
I want that the code only shows today's date cell.
Is there is a way to solve this?

I am using this code:

Private Sub Workbook_Open()
c = Month(Date)
r = Day(Date)
Sheets("Sheet1").Range("A1").Offset(r, c).Activate
ActiveCell.Interior.ColorIndex = 46
End Sub

Thanks

 

[Jacob Skaria]

Dear GIDRA

You need to set the color index to x.none and then color. In the below code
I have set the color to xlnone for 31 rows down and 12 columns to the right
from A1. You might need to need to adjust this....

Private Sub Workbook_Open()
c = Month(Date)
r = Day(Date)
Range("A1").Resize(31, 12).Interior.ColorIndex = xlNone
Sheets("Sheet1").Range("A1").Offset(r, c).Activate
ActiveCell.Interior.ColorIndex = 46
End Sub

 

[Rick Rothstein]

I *think* you need to use Resize(32, 13) in order to capture the last row
and last column since the OP appears to be using the first row/column for
headers (his Offset used the month number and day number, both of which
start from 1, not 0)...

Range("A1").Resize(32, 13).Interior.ColorIndex = xlNone

Now, using Resize(32, 13) will "uncolor" the headers along with the
calendar, but I am guessing that they are not pre-colored as part of the
design. IF they are, then you would need to use Offset(1,1) coupled with
your original Resize to skip over them...

Range("A1").Offset(1,1).Resize(31, 12).Interior.ColorIndex = xlNone

Now, with all that said, and since we *know* the calendar is offset 1 row
and column from A1 (in essence, the OP told us this), then why not just use
the proper hard-coded range and avoid the Offset/Resize stuff altogether?

Range("B2:M32").Interior.ColorIndex = xlNone

 

[Don Guillett]

try this untested idea

Private Sub Workbook_Open()
c = Month(Date)
r = Day(Date)

with Sheets("Sheet1").Range("A1")
.Offset(r-1, c).Interior.ColorIndex = 0
.Offset(r1, c).Interior.ColorIndex = 46
end with
End Sub

 

[Rick Rothstein]

I don't believe this will work on the first of any month... such a cell
would be located at the top of a column and the previous colored cell would
be located at the bottom of the previous column.