font.TintAndShade cannot change brightness of the text

Question

I select a text and change its color and it tells me this

Range("A7").Select
With Selection.Font
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = -1
End With

But when I run this code the TintAndShade fails to work. The brightness of the text in a cell cannot bot be assigned by vba. Are there any alternative of changing the brightness of the text? Or how can I make TintAndShade work?

Answer 1

I believe you are using Excel 2010. And unfortunately it is a bug in Excel 2010. Not sure if it has been rectified in Excel 2013.

Here is an alternative. Create a new workbook and paste this code in a module.

Logic: The .TintAndShade works for the cell's .Interior and hence we will use that instead of .TintAndShade for the cell's font.

Code:

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        .Range("B1:B256").Value = "Blah Blah"
        
        For i = 1 To 256
            .Range("A" & i).Interior.ThemeColor = xlThemeColorAccent2
            .Range("A" & i).Interior.TintAndShade = (i * 2 - 256) / 256
            
            .Range("B" & i).Font.Color = .Range("A" & i).Interior.Color
        Next i
    End With
End Sub

ScreenShot:

When you run this, you will see the change in the Font brightness as shown in the screenshot below. :)

Note:

For a real time scenario, create a temp sheet and in any cell, set it's .Interior.TintAndShade and then use that to set the relevant cell's font's brightness and once done, simply delete the temp sheet.