Format a column in a table based on the date
 

I have a table with the top row formatted as the dates for the given days. I
want a script or makro which looks for todays date, then highlights the
respective column in the table either with a bold border or with color.

Format a column in a table based on the date
 

Try conditional formatting with formula is =D$1=TODAY()
Note: I haven't tested this other than just now, but it works today.
--
Kevin Vaughn


"PPM at Brackmills" wrote:

I have a table with the top row formatted as the dates for the given days. I
want a script or makro which looks for todays date, then highlights the
respective column in the table either with a bold border or with color.

Format a column in a table based on the date
 

The following macro uses row 1 of sheet1 as its starting point and assumes
that there are no blank columns between the first and last data column. You
can insert the code into a general module.
================================================== ====
Sub ColorMyDay()

Dim wb As Workbook
Dim ws As Worksheet
Dim dt As Date
Dim i As Integer
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")

ws.Activate
Range("A1").Select

Selection.CurrentRegion.Select
Selection.Interior.ColorIndex = xlNone

dt = ActiveCell.Value

Do Until dt = 0
If dt = Date Then
i = ActiveCell.Column
Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
dt = 0
End If
ActiveCell.Offset(0, 1).Select
dt = ActiveCell.Value
Loop

Cells(1, i).Select

Set wb = Nothing
Set ws = Nothing

End Sub
================================================== ====
--
Kevin Backmann


"PPM at Brackmills" wrote:

I have a table with the top row formatted as the dates for the given days. I
want a script or makro which looks for todays date, then highlights the
respective column in the table either with a bold border or with color.

Format a column in a table based on the date
 

You could do it without a macro using conditional formatting

select whole table with A1 active cell, assuming top left cell of table
is A1 (adjust accordingly if not) use

Format Conditional Formatting formula is

=A$1=TODAY()

select desired formatting


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=512886

Format a column in a table based on the date
 

Kevin

Many thanks but being a newbie to VBA I can't get it to work. The sheet is
called report, the range of the table is C4-I27, row 4 has the date. Am I
asking too much for you to tailor your code for me. And then, and this is a
bit embarassing, explain how I get this into the workbook and get it to run
when the spreadsheet is opened.

Hope you can help

Mark



"Kevin B" wrote:

The following macro uses row 1 of sheet1 as its starting point and assumes
that there are no blank columns between the first and last data column. You
can insert the code into a general module.
================================================== ====
Sub ColorMyDay()

Dim wb As Workbook
Dim ws As Worksheet
Dim dt As Date
Dim i As Integer
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")

ws.Activate
Range("A1").Select

Selection.CurrentRegion.Select
Selection.Interior.ColorIndex = xlNone

dt = ActiveCell.Value

Do Until dt = 0
If dt = Date Then
i = ActiveCell.Column
Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
dt = 0
End If
ActiveCell.Offset(0, 1).Select
dt = ActiveCell.Value
Loop

Cells(1, i).Select

Set wb = Nothing
Set ws = Nothing

End Sub
================================================== ====
--
Kevin Backmann


"PPM at Brackmills" wrote:

I have a table with the top row formatted as the dates for the given days. I
want a script or makro which looks for todays date, then highlights the
respective column in the table either with a bold border or with color.