Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with dates | Excel Worksheet Functions | |||
Working with Dates | Excel Discussion (Misc queries) | |||
Working with dates | Excel Discussion (Misc queries) | |||
Working with Dates | Excel Worksheet Functions | |||
working with dates | Excel Discussion (Misc queries) |