Colour fill of empty cells in a dynamic range

Having resolved my first problem, I am now struggling to tidy up my consolidation sheet. In essence I am looking to fill the columns and rows alongside the data as well as the sells either side of the title block. I have added a screen shot which may assist in explanation. The number of columns does not change, however the number of rows do, dependant on how many sub reports are pulled into this sheet. I can (obviously) do this long hand but am looking to automate for speed and because the report will be used by others who are less competent on VBA than me (believe me that's not difficult). Would appreciate any guidance n how to achieve this.


Report.jpg

What does "title block" mean to you? To me it means row 1 through 4 which I don't think you mean. Specifics is king in this kind of stuff.

Thanks for noting my shortcomings :-)

I mean Rows 1 - 3 on row 2 either side of the text as I have 4 sheets that fill similar to this and some have say cells B2, C2 and H2, J2 empty

Hope that makes sense

Thanks for noting my shortcomings :-)

I mean Rows 1 - 3 on row 2 either side of the text as I have 4 sheets that fill similar to this and some have say cells B2, C2 and H2, J2 empty

Hope that makes sense- have put the "here's one I prepared earlier" attachment - which I did by "hand"

Report 2.jpg

Edit: Just saw your second post. This is going to be challenging with the merged cells. I will see what I can come up with.

OK - Correct in terms of A1 to I1, etc in this sheet, but would be P on two other sheet and K on a fourth one. That bit (Rows 1-3 is constant across all sheets and all reports run it is at the bottom end it is dynamic, dependant on how many workbooks have been pulled into the report

Wait what?!? I thought you said the columns wouldn't change?

Having resolved my first problem, I am now struggling to tidy up my consolidation sheet. In essence I am looking to fill the columns and rows alongside the data as well as the sells either side of the title block. I have added a screen shot which may assist in explanation. The number of columns does not change, however the number of rows do, dependant on how many sub reports are pulled into this sheet. I can (obviously) do this long hand but am looking to automate for speed and because the report will be used by others who are less competent on VBA than me (believe me that's not difficult). Would appreciate any guidance n how to achieve this.

Sorry the columns don't change in each work sheet. I am basically producing 4 different consolidated sheets, the attached being one of them these being two more.Report 3.jpgReport 4.jpg

Apologies if not making sense - its been a long day!!!!

Try this:

Please Login or Register  to view this content.

If this doesn't work you are going to need to submit your workbook so I can actually see whats going on.

Sorted absolute star many thanks