Color a Range of Cells

Hi

I have been tinkering around trying to figure this out and to be quite honest got nowhere really.

I have code (recycled) in Access that loops through a query and generates a budget file for each person.

I would like to colour a range of cells based on the number of rows in the sheet, some people may have more clients than other. The cells have no data and the colouring is to lead people to which cells they need to enter data into.

The code I have so far is below, its a work in progress as I tidy up along the way, however this is the first part I have no idea on.

Please Login or Register  to view this content.

Anything you can offer is appreciated.

Rob

Thanks for the tidy up - always good to learn a few things.

The code runs in access to produce a neatly formatted (allegedly) Excel file which attachs to an Outlook message and sends.

My "open" question is that each file contains a varying amount of rows and I want to color a range highlighting what people need to fill in.

In one file the range would be G4:G24 another one G4:G99 where G99 and G24 are the last row in the file with data.

My problem is how do I get the the G24 and G99?

Thanks again


Rob

Double Post -apologies

Thanks - I can't have a button click as this code runs in access - however I will playaround with your offset to see where that takes me.

I never open the files directly in Excel, its fully automated in Access ( or it will be when I have finished ).

250 Excel files

Thanks for quick response.

Originally Posted by davesexcel

What?
That's just the macro name.
Usually when you see submitted code with the sub button_click people start by making a button, assign macro,new. From there they can write the code, go back to the sheet click the button to test it, jeesh..

Apologies my misunderstanding

Thanks for your time and effort unfortunately the solution below did not work for 2nd file on.

Fist file was perfect 2nd failed as "Set r = Range("G4", Range("G65536").End(xlUp))" I did some googling and found that other experienced this issue when using access to create XL files.

I came up with an alternative method which given access knows how many records are in the resultant query send that variable through "RowCount" so I ended up with .Range("A2:A & RownCount)........

Thanks for again for your time and patience