highlight current day in the sheet
 

Hello all!

Well, as you'll notice I'm a newbbie in Excel, but have some stron
programming knowledge.
So, what I'm trying to do, is this:

Have an Excel sheet with days of the month (1 month per sheet in th
workbook), on 1,2,3,4... cells I have the dates of the month, and the
A, B, C, D ... are my header (it is for hours reporting). What I woul
like to do, is that each time that the user opens the spreadsheet
current date is highlighted. So, in some way I want the program t
recognize we're (e.g.) in july, and that he finds 06, and the
highlihts it. Something like:

LOOP AT "A"
IF system_date = selected_cell
set highlight_cell
endif
endloop



Well, this is how I see it, but how is it feasable trough VBA...?


Thanks! :cool

--
Message posted from http://www.ExcelForum.com

highlight current day in the sheet
 

And it is not a programming issue:

I need a simple macro that will search lets say..column C and highligh
red the cells that contain todays date (current date) and highligh
yellow any other cell in the column that is 15 days in the future fro
todays date?

Users of Excel 97 or Excel 2000 can take advantage of the ne
Conditional Formatting option. Let's cover how to do this manuall
without a macro first.

Move to cell C1.
From the menu, choose Format, Conditional Format
In the left side of the dialog, change the drop down to read "Formul
is"
In the right side of the dialog box, enter: =INT(C1)=TODAY()
Click format, Click Patterns, pick Red. Click OK
Click Add...
In the left side of the dialog, change the drop down to read "Formul
is"
In the right side of the dialog box, enter
=AND(INT(C1)TODAY(),(INT(C1)-TODAY())<16)
Click Format, Click Patterns, Pick Yellow. Click OK.
Click OK to finish assigning this conditional format to cell C1.


http://www.mrexcel.com/tip037.shtml

Thanks Mr Excel

--
Message posted from http://www.ExcelForum.com

highlight current day in the sheet
 

You have a workbook with 12 worksheets named: 01, 02, ..., 12.

Inside each worksheet, you have the day (1-28, (or 30 or 31)) in A2:A32.
(header in row 1)

If that's true, try putting this in a general module:

Option Explicit
Sub auto_open()

Dim testWks As Worksheet

Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(Format(Date, "mm"))
On Error GoTo 0

If testWks Is Nothing Then
'do nothing???
Else
Application.Goto testWks.Range("A" & Day(Date) + 1), Scroll:=True
End If

End Sub



"Ekser <" wrote:

Hello all!

Well, as you'll notice I'm a newbbie in Excel, but have some strong
programming knowledge.
So, what I'm trying to do, is this:

Have an Excel sheet with days of the month (1 month per sheet in the
workbook), on 1,2,3,4... cells I have the dates of the month, and then
A, B, C, D ... are my header (it is for hours reporting). What I would
like to do, is that each time that the user opens the spreadsheet,
current date is highlighted. So, in some way I want the program to
recognize we're (e.g.) in july, and that he finds 06, and then
highlihts it. Something like:

LOOP AT "A"
IF system_date = selected_cell
set highlight_cell
endif
endloop

Well, this is how I see it, but how is it feasable trough VBA...?

Thanks! :cool:

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

highlight current day in the sheet
 

Thanks Dave!

However, it doesn't seem to give a result...I copied exactly the sam
code.
There is also something I'd liek to know: how cane we trigger such
module without executing a macro?
e.g. I have a module I would like to be triggered each time th
WorkSheet is opened and not on a particular event - how to do this?


Thanks!

P.S. The solution I posted afterwards works fine

--
Message posted from http://www.ExcelForum.com

highlight current day in the sheet
 

The auto_open procedure runs each time you open the workbook.

Make sure it's in a general module--not behind a worksheet or ThisWorkbook.



"Ekser <" wrote:

Thanks Dave!

However, it doesn't seem to give a result...I copied exactly the same
code.
There is also something I'd liek to know: how cane we trigger such a
module without executing a macro?
e.g. I have a module I would like to be triggered each time the
WorkSheet is opened and not on a particular event - how to do this?

Thanks!

P.S. The solution I posted afterwards works fine!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

highlight current day in the sheet
 

and if you add conditional formatting to all of the rows, it will be
highlighted even after just opening it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
You have a workbook with 12 worksheets named: 01, 02, ..., 12.

Inside each worksheet, you have the day (1-28, (or 30 or 31)) in A2:A32.
(header in row 1)

If that's true, try putting this in a general module:

Option Explicit
Sub auto_open()

Dim testWks As Worksheet

Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(Format(Date, "mm"))
On Error GoTo 0

If testWks Is Nothing Then
'do nothing???
Else
Application.Goto testWks.Range("A" & Day(Date) + 1), Scroll:=True
End If

End Sub



"Ekser <" wrote:

Hello all!

Well, as you'll notice I'm a newbbie in Excel, but have some strong
programming knowledge.
So, what I'm trying to do, is this:

Have an Excel sheet with days of the month (1 month per sheet in the
workbook), on 1,2,3,4... cells I have the dates of the month, and then
A, B, C, D ... are my header (it is for hours reporting). What I would
like to do, is that each time that the user opens the spreadsheet,
current date is highlighted. So, in some way I want the program to
recognize we're (e.g.) in july, and that he finds 06, and then
highlihts it. Something like:

LOOP AT "A"
IF system_date = selected_cell
set highlight_cell
endif
endloop

Well, this is how I see it, but how is it feasable trough VBA...?

Thanks! :cool:

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

highlight current day in the sheet
 

Thanks Dave!

However, it doesn't seem to give a result...I copied exactly the sam
code.
There is also something I'd liek to know: how cane we trigger such
module without executing a macro?
e.g. I have a module I would like to be triggered each time th
WorkSheet is opened and not on a particular event - how to do this?


Thanks!

P.S. The solution I posted afterwards works fine

--
Message posted from http://www.ExcelForum.com

highlight current day in the sheet
 

The auto_open procedure runs each time you open the workbook.

Make sure it's in a general module--not behind a worksheet or ThisWorkbook.

If you wanted to highlight the cell, use the conditional formatting solution.

If you wanted to select that cell, try the auto_open code.

And opening the workbook is a particular event--so I don't quite understand your
question.

"Ekser <" wrote:

Thanks Dave!

However, it doesn't seem to give a result...I copied exactly the same
code.
There is also something I'd liek to know: how cane we trigger such a
module without executing a macro?
e.g. I have a module I would like to be triggered each time the
WorkSheet is opened and not on a particular event - how to do this?

Thanks!

P.S. The solution I posted afterwards works fine!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson