Selecting multiple worksheets sheets based on Cell value

ollieb34

New Member
Joined
Jan 27, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
Good Morning all,
I am running a macro which changes the colour of worksheets based on a cell value- This is working fine.

1633510655766.png


Id like to use the same results from the above code (that fills the worksheet tab) to also select the tabs which are green (cells <2)

All the selected sheets could be printed together without the need to manually select the individual sheets


Thanks in advance

Ollie
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
Office Version
  1. 365
Platform
  1. Windows
If you mean you want to select the sheets in the same loop just put
VBA Code:
   .Select False
after your
.Tab.Color = vbGreen

Then when you want to print
VBA Code:
ActiveWindow.SelectedSheets.PrintOut Copies:=1
 

ollieb34

New Member
Joined
Jan 27, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
If you mean you want to select the sheets in the same loop just put
VBA Code:
   .Select False
after your
.Tab.Color = vbGreen

Then when you want to print
VBA Code:
ActiveWindow.SelectedSheets.PrintOut Copies:=1
its not liking that- is that the correct location for the code?

1633519214890.png

1633519167657.png
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
Office Version
  1. 365
Platform
  1. Windows
It looks fine to me.
Just to be sure can you copy this in and run it.
VBA Code:
Sub test()

    Dim sht As Worksheet
    
    For Each sht In ThisWorkbook.Worksheets
        With sht
            If IsNumeric(.Range("A1")) And .Range("A1") < 2 Then
                .Tab.Color = vbGreen
                .Select False
            Else
                .Tab.Color = vbRed
            
            End If
        End With
     
    Next sht

End Sub
 

ollieb34

New Member
Joined
Jan 27, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
It looks fine to me.
Just to be sure can you copy this in and run it.
VBA Code:
Sub test()

    Dim sht As Worksheet
   
    For Each sht In ThisWorkbook.Worksheets
        With sht
            If IsNumeric(.Range("A1")) And .Range("A1") < 2 Then
                .Tab.Color = vbGreen
                .Select False
            Else
                .Tab.Color = vbRed
           
            End If
        End With
    
    Next sht

End Sub
Unfortunately still failing for the same issue
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
Office Version
  1. 365
Platform
  1. Windows
I thought it might be protection but workbook protection causes the Tab Color to fail and not the select statement.
Are you able to share the workbook using dropbox or google drive or onedrive etc ?

Do you have any Event Macros in the workbook ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,574
Messages
5,770,928
Members
425,652
Latest member
Pemby

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
Top