VBA Format Very Hidden Sheets

[nitrammada]

Hello Folks,
I have some code that finds the last row of data on my very hidden sheet, rules it off by colouring the last row without data. The code works fine if the sheet is visible, but I have 100's of these sheets that are very hidden. I've tried to run the code on these very hidden sheets but can't get it to run. Is anyone able to help me please? My code is below, if any one can assist i would be most grateful.
Thanks in advance
Regards
Adam

Sub OffsetToAllShts3()
'Colour last row to all sheets
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect

Dim lWS_Visible_State As Long
Dim ws As Worksheet
    'Apply Format to all very hidden sheets

    For Each ws In Worksheets
        lWS_Visible_State = ws.Visible
        ws.Visible = xlSheetVisible

        If ws.Range("A1").Value = "L2" Then
            Range("D7").End(xlDown).Offset(1, -2).Select
            Range(ActiveCell, ActiveCell.Offset(0, 35)).Select
                With Selection
                    .Interior.Color = 14083324
                End With
        End If
        ws.Visible = lWS_Visible_State
    Next ws

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[MARK858]

Oh No! so late, I feel bad

No need to feel bad, just can't sleep tonight

 

[nitrammada]

Then I'll need to see the actual workbook, you can upload it to a free file sharing site like www.box.com or www.dropbox.com, mark the file for sharing and paste the link it provides in the thread.

Make sure that you alter any sensitive information before uploading.

I won't be looking at it until tomorrow night as it is almost 3am here.

Hi Mark858, here is the link to dropbox.

102SOEMT3.6 master wi qts.xlsm

Shared with Dropbox

www.dropbox.com

The file is called 102SOEMT3.T master wi qts.xlsm
On Sheet1 there are some buttons to open and close the very hidden sheets, but I'm sure you will have a better way to do that. The sheets that I want to apply this formatting to, as you would already have guessed, is only those the have L2 in cell A1. The code you gave me is in Module16_Offset. Cheers for now. Good night.

 

[MARK858]

I haven't identified the cause but I can tell you that it is to do with Sheet2. I won't be able to look deeper tonight I am afraid.

 

[nitrammada]

I haven't identified the cause but I can tell you that it is to do with Sheet2. I won't be able to look deeper tonight I am afraid.

Ah Ha, thanks Mark858, can delete Sheet2 if you like, I was only using it for testing.

 

[MARK858]

I did delete it before posting and the code doesn't error out without it

 

[Alex Blakenburg]

@MARK858 - just for interests I had a look.
Sheet2 has nothing below D7.
So xlDown was taking it down to the last row in the sheet and offset(1,-2) was pushing it off the edge.

Mark858, thanks again for such quick response, this time I get "Run-time error '1004': application-defined or object-defined error" on this line:
Set MyRng = ws.Range("D7").End(xlDown).Offset(1, -2)

 

[nitrammada]

I did delete it before posting and the code doesn't error out without it

Hello Mark858, I removed Sheet2 and it works perfectly, so without putting you to any more bother I have marked your post #5 as the solution. Thank you so much for spending your time (in the early hours of the morning) to help me, you have saved me hours of work. Appreciate your efforts so much, many thanks.
Kind regards
adam

 

[MARK858]

You're welcome from all of us