Selecting multiple worksheets sheets based on Cell value

[ollieb34]

Good Morning all,
I am running a macro which changes the colour of worksheets based on a cell value- This is working fine.

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

 

[Alex Blakenburg]

If you mean you want to select the sheets in the same loop just put

   .Select False

after your
.Tab.Color = vbGreen

Then when you want to print

ActiveWindow.SelectedSheets.PrintOut Copies:=1

 

[ollieb34]

If you mean you want to select the sheets in the same loop just put

   .Select False

after your
.Tab.Color = vbGreen

Then when you want to print

ActiveWindow.SelectedSheets.PrintOut Copies:=1

its not liking that- is that the correct location for the code?

 

[Alex Blakenburg]

It looks fine to me.
Just to be sure can you copy this in and run it.

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]

It looks fine to me.
Just to be sure can you copy this in and run it.

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]

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 ?