Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Today only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Today only
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 -- If this post helps click Yes --------------- Jacob Skaria "GIDRA" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Today only
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 -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... 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 -- If this post helps click Yes --------------- Jacob Skaria "GIDRA" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Today only
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "GIDRA" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Today only
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. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "GIDRA" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Row() and Today() | Excel Discussion (Misc queries) | |||
NOW AND TODAY | Excel Worksheet Functions | |||
Keep 6 months of dates from Today to (Today + 6 Months) | Excel Programming | |||
IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
=IF(OR(TODAY() |
Excel Discussion (Misc queries) |