highlight current day in the sheet

E

Ekser

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
 
E

Ekser

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
 
D

Dave Peterson

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
 
E

Ekser

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
 
D

Dave Peterson

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.
 
B

Bob Phillips

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)
 
E

Ekser

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
 
D

Dave Peterson

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.
 
Top