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?

Alimentation

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.

Ona

Answer 2

Another solution is to use .color instead of .interior.TintSndShade. Find the color and tint you want from the home tab and font category in excel. Then record a macro of changing the cell's font color to that what you want. Then use the "more color options" and slide the darker/lighter bar up or down very slightly. This will give you .color property in your recorded excel macro.

Bundle

Good answer. But if you can make an example macro that would change the font color with a couple of example cells, it will be a great answer. The question is old, OP already solved his problem; but someone searching for a similar problem may find this question and having a small example in your answer will help him. – Jeopardize

Answer 3

I also found another solution to this, maybe a little tricky... I created a cell with the required font color but with no text and copy this cell to where I need the color.

Dim texxt As String
texxt = Range(N_Range).Value
Range("R3").Copy  'this is my preset cell
Range(N_Range).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = texxt

Tipster