VBA Format Very Hidden Sheets

nitrammada

nitrammada

Board Regular
Joined
Oct 10, 2018
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code: 
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Untested but try the code below (can't tell by what you have written but If your hidden sheets are protected then you'll also have to unprotect and re-protect them inside the loop)

VBA Code: 
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, MyRng As Range
    '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
            Set MyRng = ws.Range("D7").End(xlDown).Offset(1, -2)
            Range(MyRng, MyRng.Offset(0, 35)).Interior.Color = 14083324
        End If
        '        ws.Visible = lWS_Visible_State
    Next ws

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
 
Upvote 0
MARK858 said:
Untested but try the code below (can't tell by what you have written but If your hidden sheets are protected then you'll also have to unprotect and re-protect them inside the loop)

VBA Code: 
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, MyRng As Range
    '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
            Set MyRng = ws.Range("D7").End(xlDown).Offset(1, -2)
            Range(MyRng, MyRng.Offset(0, 35)).Interior.Color = 14083324
        End If
        '        ws.Visible = lWS_Visible_State
    Next ws

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
Click to expand...
Hello Mark858,
Thank you for your quick response, works perfectly, thank you so much. And thank you for your tip on the unprotect and protect function, i wasn't really sure where to put that to be honest but I have it working now, thank you. Can I ask one other thing please, if I now wanted to clear any redundant values below that line using the same type of offset function to specify my range to clear, how would I do that?
 
Upvote 0
MARK858 said:
Untested but try the code below (can't tell by what you have written but If your hidden sheets are protected then you'll also have to unprotect and re-protect them inside the loop)

VBA Code: 
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, MyRng As Range
    '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
            Set MyRng = ws.Range("D7").End(xlDown).Offset(1, -2)
            Range(MyRng, MyRng.Offset(0, 35)).Interior.Color = 14083324
        End If
        '        ws.Visible = lWS_Visible_State
    Next ws

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
Click to expand...
Hello Mark858, I just noticed that the code you sent, whilst it works perfectly when the sheet is visible, it didn't work on the very hidden sheets. Is there a way to get your code to work whilst the sheets are in the very hidden state? Or, is there a way your code could make the very hidden sheets visible, run the code, then make them very hidden again? Sorry to bother you again.
 
Upvote 0
Code works fine for me when the sheets are hidden or very hidden

As for your other question

Rich (BB code): 
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, MyRng As Range
    'Apply Format to all very hidden sheets

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

        If ws.Range("A1").Value = "L2" Then
            Set MyRng = ws.Range("D7").End(xlDown).Offset(1, -2)
            Range(MyRng, MyRng.Offset(0, 35)).Interior.Color = 14083324
            Range(ws.Cells(MyRng.Row + 1, MyRng.Column), ws.Cells(ws.Rows.Count, MyRng.Offset(0, 35).Column)).ClearContents
        End If
        '        ws.Visible = lWS_Visible_State
        ws.Protect
    Next ws

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
 
Upvote 0
Solution
MARK858 said:
Code works fine for me when the sheets are hidden or very hidden

As for your other question

Rich (BB code): 
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, MyRng As Range
    'Apply Format to all very hidden sheets

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

        If ws.Range("A1").Value = "L2" Then
            Set MyRng = ws.Range("D7").End(xlDown).Offset(1, -2)
            Range(MyRng, MyRng.Offset(0, 35)).Interior.Color = 14083324
            Range(ws.Cells(MyRng.Row + 1, MyRng.Column), ws.Cells(ws.Rows.Count, MyRng.Offset(0, 35).Column)).ClearContents
        End If
        '        ws.Visible = lWS_Visible_State
        ws.Protect
    Next ws

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
Click to expand...
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)

Any thoughts?
 
Upvote 0
MARK858 said:
Check the sheet protection and that you have something in D7 on each sheet
Click to expand...
Hmmm. I checked and all cells at D7 have data in them, there are 141 very hidden sheets. The code does work on the very hidden sheets as you say, thank you, great work on that one. However, I still get the same error msg when I run the code, there must be something on perhaps one sheet that's tripping it up.
 
Upvote 0
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.
 
Upvote 0
MARK858 said:
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.
Click to expand...
Oh No! so late, I feel bad, so sorry. I am so grateful for your help, please, no hurry. I will send you a link to drop box. Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
username access to different worksheet VBA
Replies
4
Views
285
cilantro00
C
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
519
Joe4
Joe4
C
  • Solved
VBA to unhide all sheets but return to active sheet
Replies
8
Views
638
colzre
C
E
  • Solved
Locking of cells with protected sheet not working
Replies
5
Views
415
Eurekaonide
E
L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
182
RoryA
RoryA

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top