VBA code to get RGB color for a tab

Col Werner

New Member
Joined
Jul 5, 2018
Messages
5
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Try this:
Code:
Sub Tab_Color()
'Modified 7/5/18 11:20 PM EDT
ans = ActiveSheet.Tab.Color
MsgBox ans
End Sub
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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..
 
Upvote 0
Try this:

Code:
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
 
Upvote 0
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.
 
Upvote 0
This script will set all the shapes on your sheet to the active sheet tab color. Does not matter what their names are:
Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top