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.

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

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.

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.

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.