Working with Dates
 

Hi

I'm trying to do some automation in Excel, and having no luck. I'd really
appreciate some help.

In cell A1 I have entered a date, and then the rest of the row has formulas
along the lines of A1+1, B1+1...

What I would like is the current today to be highlighted via conditional
formatting, and I'd like the spreadsheet to automatically open at the
current/today's date.

Any assistance gratefully received.

Cheers, Ty

Working with Dates
 

To get the workbook to open at the current date you can use a workbook open
event like this: (I have assumed the sheet is called DateSheet - change this
to suit)

Private Sub Workbook_Open()
Dim Tday As Range
Sheets("DateSheet").Activate
With ActiveSheet.Rows(1)
Set Tday = .Find(Date, LookIn:=xlValues)
End With
If Not Tday Is Nothing Then
Tday.Activate
End If
End Sub

This is a workbook event and should be pasted into the ThisWorkbook code
module - right click the green excel Icon next to the File menu and select
View Code.

I am not sure if you are wanting to automate the conditional formatting as
well. If so the code would look something like this (assuming the sheet with
the dates is active)

Dim endcol As Integer
endcol = Cells(1, Columns.Count).End(xlToLeft).Column
Range("A1").Activate
With Range(Cells(1, 1), Cells(1, endcol))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=A$1=TODAY()"
.FormatConditions(1).Interior.ColorIndex = 35
End With

Hope this helps
Rowan

"Tywardreath" wrote:

Hi

I'm trying to do some automation in Excel, and having no luck. I'd really
appreciate some help.

In cell A1 I have entered a date, and then the rest of the row has formulas
along the lines of A1+1, B1+1...

What I would like is the current today to be highlighted via conditional
formatting, and I'd like the spreadsheet to automatically open at the
current/today's date.

Any assistance gratefully received.

Cheers, Ty

Working with Dates
 

Ty,
For the open date you could Name the cell where you want the date
to appear, and put this into the WorkBook module.

Private Sub Workbook_Open()
Range("MyDateCell") = Date
End Sub

Change the MyDateCell to the name of your cell.

This help?
Dave

Tywardreath Wrote:
Hi

I'm trying to do some automation in Excel, and having no luck. I'd
really
appreciate some help.

In cell A1 I have entered a date, and then the rest of the row has
formulas
along the lines of A1+1, B1+1...

What I would like is the current today to be highlighted via
conditional
formatting, and I'd like the spreadsheet to automatically open at the
current/today's date.

Any assistance gratefully received.

Cheers, Ty


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=390425