Format a column in a table based on the date

P

PPM at Brackmills

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

Kevin Vaughn

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

Kevin B

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
======================================================
 
D

daddylonglegs

You could do it without a macro using conditional formatting

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

Format > Conditional Formatting > formula is

=A$1=TODAY()

select desired formattin
 
P

PPM at Brackmills

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
 
Top