Setting Excel cell formats from Access VBA

[AndrewDB]

Hi All. I need to export data to an Excel spreadsheet and set the cell
backcolors depending on the data values. Hoe do I set the cell backcolors?
Thanks

Regards

 

[JH]

Are you trying to export to a new spreadsheet or one that is already created?


If you are exporting to an already created one, you should be able to use
the "conditional formatting" options to set the bgcolors and fonts. Last
time I did an export to excel with conditional formatting already set, I
didn't lose the formatting conditionals.

If you are exporting to a new spreadsheet, I'm afraid I am of no assistance
there.

 

[Douglas J. Steele]

You have to use Automation.

I have an example in my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[AndrewDB]

Hi JH

I am exporting to a new spreadsheet. Just a thought - If I set the formating
of a cell in a spreadsheet (using RGB colours) that I can use as a template,
will I be able to copy that backcolor to the cell I want to use?

Regards

 

[AndrewDB]

Hi Doug,

I've downloaded and looked at your example. So thats what MVP means!!
(M)ost (V)ery im(P)ressed!!

Just kidding. The example is a great help. But still could not solve my
problem.
My question - Is there a way to set the cell backcolor using a RGB value?

Best Regards

 

[Douglas J. Steele]

While I didn't have a specific example of that particular piece of
formatting, I thought the article told you how to do whatever you needed to
do by recording a macro in Excel that does it and looking at the VBA
generated by that.

 

[AndrewDB]

Thanks Doug. Appreciate your help.

Regards
Andrew