Auto Format

[davisto01]

Hello,

I am using MS Access to generate daily error reports and exporting them to excel spreadsheets and sending them out via email. The exporting part is easy however I would like to shade out all of the records prior to today.

Basically if the date is not today, I need the entire row to be gray. If it is today it can remain with the default formatting.

Right now I don't see this even being possible because the reports are generated automatically by access and whatever process it uses to export its reports into spreadsheets. I just wish there were a formatting option.

Is there a way or am I screwed?

TD

 

[BrainB]

I would try Conditional Formatting, something like :-
Formula Is : =$A1<NOW() and have the background grey.

Interestingly this would be dynamic in the sense that sheets viewed later on would get the grey background.

The trick is to set up a pre-formatted worksheet to accept the output from Access (ie. easiest way is to use an existing output and format that). Keep this workbook for your daily output and make copies each day with a different name. When you next export to this book/sheet the old data is automatically removed and replaced with the new - as well as retaining your Excel formatting.



Regards
BrianB
** Let us know if you get something that works !
================================

 

[lucrahouse]

Another option

Sub ShadeRowsGray()

Dim Today As Date
Dim MyRowCount As Integer
Dim MyColumnCount As Integer
Dim x As Double

Range(&quot;a1&quot.Select
Set Table1 = ActiveCell.CurrentRegion
MyRowCount = Table1.Rows.Count
MyColumnCount = Table1.Columns.Count
Today = DateSerial(Format(Date, &quot;yy&quot, Format(Date, &quot;mm&quot, Format(Date, &quot;dd&quot)

For x = 1 To MyRowCount
If Table1(x, 1) <> Today Then
Range(Cells(x, 1), Cells(x, MyColumnCount)).Interior.ColorIndex = 15
Else
Range(Cells(x, 1), Cells(x, MyColumnCount)).Interior.ColorIndex = 2
End If
Next x

End Sub

But I know it can be done better, if I had more time to work on it. Maybe this can get you started. Good luck.