Formatting Excel spreadsheet where data exists

[timotai]

Hi

I have a database which creates a excel spreadsheet. I want to be able to format the colours of the spreadsheet cells fo rthe background and font. However the tricky bit is if there is data in a cell within a given range I want it to be one set of colours and where there is no data I want to use other colours.

I have tried a find but cannot get it to work, that was my desired option, I also tried to apply a conditional format but also cannot get that to work from access either.

I think my problem lies in an insufficient understanding of using VB to control Excel as an object.

All help is greatly greatly appreciated.

Thanks

Tim

 

[VBslammer]

Check out the UsedRange property of the worksheet object:

[green]'get address of first used cell[/green]
=Worksheets(1).UsedRange.Rows(1).Address
[green]'get address of last used cell[/green]
=Worksheets(1).UsedRange.Rows(Worksheets(1).UsedRange.Rows.Count).Address
[green]'select the used range[/green]
Worksheets(1).UsedRange.Select
[green]'set the backgound color[/green]
Worksheets(1).UsedRange.Interior.Color = RGB(100, 200, 100)

VBSlammer

"You just have to know which screws to turn." - Professor Bob