VBA code to get RGB color for a tab

[Col Werner]

Hi!

I'm wanting to discover the VBA code to tell me what RGB tab color is being used in a sheet/tab.

Can anyone help?

Cheers,

Col

 

[My Aswer Is This]

Try this:

Sub Tab_Color()
'Modified 7/5/18 11:20 PM EDT
ans = ActiveSheet.Tab.Color
MsgBox ans
End Sub

 

[Col Werner]

Thanks "My aswer Is This".

I tested this by creating a Tab color with the following colors Red 0, Green 0, Blue 255 and received the following response "16711680"

This may equal the RGB scale (R 0, G 0, B 255), if so, any idea how to convert it back to RGB?

Also, assuming we can extact the answer in "RGB" terms - do you know the code that would paste the "R" into cell B1, "G" into cell B2 & "B" into cell C3?

Thanks in advance

 

[My Aswer Is This]

No I really do not know why Blue shows up as

16711680​

And it would be nice to know what is your ultimate goal here.
I figured earlier you would want more then this.

Why would you want to do this:

would paste the "R" into cell B1, "G" into cell B2 & "B" into cell C3?​

I believe this is not the ultimate goal

 

[Col Werner]

Hi - good question.

I'm looking to get the tab color so that I can aply it to a cell and also to a shape. Need to do this on a mass scale, hecnce the question..

 

[My Aswer Is This]

Try this:

Sub Shape_Color()
'Modified 7/6/18 1:30 AM EDT
Dim ans As String
ans = ActiveSheet.Tab.Color
ActiveSheet.Shapes("Rectangle 5").Fill.ForeColor.RGB = ans
ActiveCell.Interior.Color = ans
End Sub

 

[Col Werner]

Champion! Had to rename my shape to "Rectangle 5" using ALT F10 but it works - A+ effort thanks!

 

[My Aswer Is This]

I gave you a generic shape name in my script since I did not know the name of your shape.
Just change the shape name in the script if you want.

Sounds like you plan to do this many times on many sheets.
You said:

Need to do this on a mass scale, hecnce the question..​

If you need more help on this project let me know.

 

[My Aswer Is This]

This script will set all the shapes on your sheet to the active sheet tab color. Does not matter what their names are:

Sub Many_Shapes()
'Modified 7/6/18 2:25 AM EST
Dim ans As String
ans = ActiveSheet.Tab.Color
Dim s As Shape
For Each s In ActiveSheet.Shapes
    s.Fill.ForeColor.RGB = ans
Next

 

[My Aswer Is This]

This one script would work on all the sheets in your workbook.
All the shapes on all your sheets would have the same fill color as your Tab colors.

Sub All_Shapes_All_Sheets()
'Modified 7/6/18 2:45 AM EST
Application.ScreenUpdating = False
Dim ans As String
Dim s As Shape
For i = 1 To Sheets.Count
    For Each s In Sheets(i).Shapes
        ans = Sheets(i).Tab.Color
        s.Fill.ForeColor.RGB = ans
    Next
Next
Application.ScreenUpdating = True
End Sub