font.TintAndShade cannot change brightness of the text

brightness, excel, fonts, text, vba

Solution

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.

Problem

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?

Original source