Colour fill of empty cells in a dynamic range

[Coems]

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

[stnkynts]

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.

[Coems]

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

[Coems]

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

[stnkynts]

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.

[Coems]

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

[stnkynts]

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.

[Coems]

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!!!!

[stnkynts]

Try this:

Sub RunMe()
Dim ws As Worksheet
Dim rCell As Range
Dim LR As Long, LC As Long

Application.ScreenUpdating = False

For Each ws In Worksheets
    LR = ws.Range("B" & Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(LR - 1, Columns.Count).End(xlToLeft).Column + 1
    For Each rCell In ws.Range(ws.Cells(1, 1), ws.Cells(LR, LC))
        If rCell.Value = "" And rCell.Interior.ColorIndex = xlNone Then
            rCell.Interior.ColorIndex = 6 'change the color to whatever you want
        End If
    Next rCell
Next ws

Application.ScreenUpdating = True

End Sub

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

[Coems]

Sorted absolute star many thanks