for each sheet in worksheets

[lezawang]

Hi the code below does not work. Complaining about

xsheet.Tab.ColorIndex = i

I did similar idea with cells and everything was fine. Please see the second code. Thank you so much.

Sub mysheets()
    Dim xsheet As Worksheet
    Dim i As Integer
    i = 0
    For Each xsheet In Worksheets
        xsheet.Tab.ColorIndex = i
        i = i + 1
    Next xsheet
End Sub

Sub myselection3()
 Dim xcell As Range
 Dim i As Integer
 For Each xcell In Selection
    xcell.Value = i
    xcell.Interior.ColorIndex = i
    i = i + 1
Next xcell
End Sub

 

[Rick Rothstein]

This worked for me...

Sub ColorTabs()
  Dim X As Long
  For X = 1 To Sheets.Count
    Sheets(X).Tab.ColorIndex = 1 + (X Mod 56)
  Next
End Sub

 

[BlueHornet]

Start with a different value of i. The starting value of 0 won't work.

 

[lezawang]

Thank you all. Yes if I start with i=1 then I am ok. Not sure why? I did the same thing for Cells and it works even when I=0

Sub myselection3()
 Dim xcell As Range
 Dim i As Integer
 i = 0
 For Each xcell In Selection
    xcell.Value = i
    xcell.Interior.ColorIndex = i
    i = i + 1
Next xcell
End Sub

Thank you again

 

[RoryA]

It's a quirk. With the Interior it treats colorindex 0 as the same as xlcolorindexnone.

 

[DanteAmor]

Remember the recommendations for 56 colors.

https://www.mrexcel.com/forum/excel...h-cell-selection-post5275208.html#post5275208