Excel vba font color keeps shoeing white, instead of gray

Hi! I have code that changes the font in a column of a pivot table to a dark gray, but when I run the macro, its turns the font white. I don't know if it has "View Gridlines" turned off (which are the same dark gray color I'm trying to turn the font). Can anyone help? I'm going crazy. FontColorExample.docx  
Sub Fixfont4()
'
' Fixfont4 Macro
'

'
    Range("E4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Formula1", xlDataAndLabel, _
        True
    Selection.Font.Italic = False
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
    Selection.Font.Bold = True
    Selection.Font.Italic = True
End Sub

Open in new window

McQMomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
I suspect the problem is that the macro is applying things here:


    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With

If you take out those lines, what happens?

'    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
'    With Selection.Font
'        .ThemeColor = xlThemeColorDark1
'        .TintAndShade = -0.499984740745262
'    End With
0
McQMomAuthor Commented:
Then I get all my pivot columns in regular font (Including my forecast) and my Total Shipped & Open column (A calculated item column) in bold.
0
rspahitzCommented:
Looks like it might be an anomaly in the new Excel, based on the color themes.

Restore the original code you had and add something like this just before "Selection.Font.Bold = True"


ActiveSheet.PivotTables("PivotTable1").PivotSelect "Values", xlDataAndLabel, True
    Range("F10:I12").Select
    With Selection.Font
        '.ThemeColor = xlThemeColorDark1
        '.TintAndShade = -0.499984740745262
        .Color = &H808080
        .TintAndShade = 0
    End With

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

McQMomAuthor Commented:
Still showing up as white. Dang - it's driving me crazy!
0
rspahitzCommented:
Post your code again so I can test it on my end.
I saw the white appear but the above code fixed it for me in v2010.
0
McQMomAuthor Commented:
I seem to be able to get it in a dark blue now, but still not in the dark gray. It still turns white...
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
    With Selection.Font
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.249977111117893
    End With
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Formula2", xlDataAndLabel, _
        True
    Selection.Font.Italic = True
    Selection.Font.Bold = True

Open in new window

0
rspahitzCommented:
Looks like you didn't understand that I wanted you to add my code into yours, like this:

 
Sub Fixfont4()
'
' Fixfont4 Macro
'

'
    Range("E4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Formula1", xlDataAndLabel, _
        True
    Selection.Font.Italic = False
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Values", xlDataAndLabel, True
    Range("F10:I12").Select
    With Selection.Font
        '.ThemeColor = xlThemeColorDark1
        '.TintAndShade = -0.499984740745262
        .Color = &H808080
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Selection.Font.Italic = True
End Sub

Open in new window


However, I'm not sure that "Values" is the correct piece so you may need to adjust that to another Pivot setting.
0
McQMomAuthor Commented:
Still all just white, I think I'm just going to leave it blue - :( White-font-example.docx
0
rspahitzCommented:
Any chance of sending the workbook?
At least then I can see if the same problem happens on my end.
0
McQMomAuthor Commented:
I'll try and mutate the numbers enough to send it to you.
0
McQMomAuthor Commented:
Here it is.  Font-Color.xlsm
0
rspahitzCommented:
OK, I see the problem now.  The Forecast group is showing up as white.  I was trying to change a different group.

In the subroutine, change this so the indicated lines are commented (' at the beginning of the line):

...
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
    With Selection.Font
'        .ThemeColor = xlThemeColorDark1
'        .TintAndShade = -0.499984740745262
'    End With
'
'    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Values", xlDataAndLabel, True
'    Range("F10:I12").Select
'    With Selection.Font
        '.ThemeColor = xlThemeColorDark1
        '.TintAndShade = -0.499984740745262
        .Color = &H808080
        .TintAndShade = 0
    End With
...

Your resulting macro will look like this:
 
Sub Fixfont4()
'
' Fixfont4 Macro
'

'
    Range("E4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Formula1", xlDataAndLabel, _
        True
    Selection.Font.Italic = False
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Forecast", xlDataAndLabel, _
        True
    With Selection.Font
        .Color = &H808080
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Selection.Font.Italic = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
McQMomAuthor Commented:
Perfect! Thank you so much for your perseverance. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.