Tint and Shade a Worksheet Tab with VBA? Howdy, I am adding several worksheets t

Question

Tint and Shade a Worksheet Tab with VBA?

Howdy,

I am adding several worksheets to a work book and want each tab to be a separate color. I thought I could set this up via Arrays, and use RGB color. I have seen RGB color work for tint and shading of cells, but I guess it doesn't work for tabs? How could I implement this? (I could look up the VBA colors, like .2034483) but would like to learn how to either convert my RGB values to that decimal format, or how to otherwise set a bunch of tab colors:

Dim wsNamesSize as Integer

Dim newWSnames as Variant

Dim colorArray(7,2) as Integer 'I have 8 tabs, with three colors each (1 for R, 1 for G, 1 for B)

newWSnames = ("Superhero","Address","State","City","Crime","Phone","Fax","Map")

wsNamesSize = Application.CountA(newWSnames)

' The below group will create various multidimensional Arrays

colorArray(0,0) = 255 'sets the first R to 255

colorArray(0,1) = 0

colorArray(0,2) = 0

colorArray(1,0) = 0

colorArray(1,1) = 255

colorArray(1,2) = 0

colorArray(2,0) = 0

colorArray(2,1) = 0

colorArray(2,2) = 255

colorArray(3,0) = 100

colorArray(3,1) = 100

colorArray(3,2) = 100

...so on and so on until

colorArray(7,0) = 50

colorArray(7,1) = 100

colorArray(7,2) = 255 ' I now have 8 groups of 3 RGB colors, one set for each new tab

For i = 1 to wsNamesSize

   Sheets.Add After:=Sheets(Sheets.Count) ' add new worksheet

   Sheets(ActiveSheet.Name).Name = newWSnames(i-1) ' name it from what is listed in the array above

   With Sheets(ActiveSheet.Name).Tab;

        .ThemeColor = xlThemeColorAccent6

        .TintAndShade = RGB(colorArray(i-1,0),colorArray(i-1,1),colorArray(i-1,2));

   End With

Next

This doesn't quite work. It does shade the tab, but every tab is shaded Orange. A) Is this a good idea of how to get this done (using the multidimensional Array)?? and B) Why isn't it working like I'd think it would?

Thanks for any ideas/help/advice!!

Explanation / Answer

TintAndShade is not an RGB color but a value between -1 (darkest) and 1 (lightest).; Change

    With Sheets(ActiveSheet.Name),.Tab

        .ThemeColor = xlThemeColorAccent6;

        .TintAndShade = RGB(colorArray(i-1,0),colorArray(i-1,1),colorArray(i-1,2))

    End With

to

    With Sheets(ActiveSheet.Name).Tab

        .Color = RGB(colorArray(i-1,0),colorArray(i-1,1),colorArray(i-1,2))

    End Withh!

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.