VBA - Read-only Tab Font Color

[dhancy]

Hello,

I have seen articles saying that we cannot change a tab font color in VBA. Excel will automatically determine the font color based on the background color selected.

But is it possible to read the font color of a tab?

Thanks,
Dennis

 

[LazyBug]

Hello!

But is it possible to read the font color of a tab?

?ActiveSheet.Tab.Color

 

[dhancy]

Thanks, but isn't that the background color of the tab, and not the font color?

 

[Fluff]

As far as I know, there is no way of finding the font colour.
Why do you ask?

 

[dhancy]

I have a request to duplicate the colour scheme on a given tab in a given range of cells on a worksheet.

As you noted, reading the tab's background colour is straightforward (activesheet.tab.color). However we have some tabs with a black background. When I duplicate that in a range of cells, the font colour is still black, making it impossible to read. I was hoping to read the tab's font colour as well so I could apply that to the range of cells also.

 

[LazyBug]

Thanks, but isn't that the background color of the tab, and not the font color?

My fault, read it inattentively.

 

[dhancy]

No worries.. Appreciate you taking time to respond And I see I was also being inattentive. I "assumed" the second person to respond to this was you, but now I see it is someone else. It's going to be one of those days.. LOL

 

[Fluff]

Ok, you could use this function by @Rick Rothstein

Function TextColorToUse(BackColor As Long) As Long

'  This function returns the color to use for
'  text to make it readable on a dark background
'  Code by of Rick Rothstein
  Dim Luminance As Long
  Luminance = 77 * (BackColor Mod &H100) + _
              151 * ((BackColor \ &H100) Mod &H100) + _
              28 * ((BackColor \ &H10000) Mod &H100)
  '  Default value of TextColorToUse is 0-Black, set
  '  it to White if the Luminance is less than 32640
  If Luminance < 32640 Then TextColorToUse = vbWhite
End Function

And use it like

Sub dhancey()
With Range("A1:C20")
   .Interior.Color = .Parent.Tab.Color
   .Font.Color = TextColorToUse(.Parent.Tab.Color)
End With
End Sub

 

[dhancy]

This is perfect. Thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.