VBA Change font color based on backgroud color in pivot table

Question

I'm trying to write a VBA code to change a font color of a cell based on a background color of that cell. Generally, to "hide" record names in two columns ("ID" and "Name"). Cells are colored using conditional formatting.

I managed to find a way to change a font using only 1 color but don't know how dynamically get background color of a cell and use it as a font color.
Simple .Font.Color = .Interior.ColorIndex doesn't work...

Sub Color_text_black()  
   Dim c As Range
    With ActiveSheet.PivotTables("PivotTable2")

    With Intersect(.PivotFields("Name").DataRange.Cells, .TableRange1)
        .Font.Bold = False
        .Font.Color = 1 '.Font.Color = .Interior.ColorIndex doesn't work

    End With
    End With
End Sub

Also maybe there should be another way to get the background color, I tried to use For loop but it always sends me an error.

Sub Color_text()
    Dim c As Range

    With ActiveSheet.PivotTables("PivotTable1")

        For Each c In .PivotFields("Name").DataRange.Cells
          .Font.Bold = False
          .Font.Color = .Interior.ColorIndex
        Next

    End With

End Sub

Thank you for any help!

If you're using CF then you should use `DisplayFormat.Interior.ColorIndex` — Tim Williams, Oct 17 '18 at 21:00
`.Color`, not `.ColorIndex`. The two are not the same thing. — BigBen, Oct 17 '18 at 21:02
What @BigBen said - using `Range().Font.Color = Range().Interior.Color` worked just fine for me. — dwirony, Oct 17 '18 at 21:04
@ValonMiller Yes, I'm using conditional formatting for those cells. — Tart, Oct 17 '18 at 21:12
@TimWilliams Could you tell where should I use DisplayFormat.Interior.ColorIndex? — Tart, Oct 17 '18 at 21:13
@dwirony Could you post the code that worked for you? When I use .Color instead of .ColorIndex nothing happens.. — Tart, Oct 17 '18 at 21:14
@Art sorry I didn't see you were using cf. So you do need .displayformat in there. — dwirony, Oct 17 '18 at 21:35
@TimWilliams sorry but the code still doesn't work, nothing happens. Have you used the one with "For loop" or "intersect"? Is it possible to share your code? — Tart, Oct 18 '18 at 13:17
@TimWilliams could you explain what does this line do and where should I plug it? .PivotFields("Name").DataRange.NumberFormat = ";;;""""" — Tart, Oct 18 '18 at 13:18
@dwirony no worries, thanks for the suggestion. Did you make the code work by any chance? I still cannot find the right solution... — Tart, Oct 18 '18 at 13:21
that line would replace the loop where you're adjusting the font color. It's a custom number format and there are good explanations to be found via google. Eg: https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68 — Tim Williams, Oct 18 '18 at 14:52
@TimWilliams thank you, the approach did work! And if I need to "hide" another column, I should just add the same line for that column or is there a way to add both columns in one line? When I try to write `.PivotFields("ID", "Name").DataRange.NumberFormat = ";;;"""""` - an error pops up.. Also could you tell what I should change in the ` = ";;;""""" ` to "unhide" records names? — Tart, Oct 18 '18 at 17:11
@TimWilliams I think I found a way to "unhide" records' names using `.PivotFields("Name").DataRange.NumberFormat = "General"`, thank you! — Tart, Oct 18 '18 at 17:20

Tim Williams · Answer 1 · 2018-10-19T05:39:09.900

1

Note there is an alternative approach to hiding the text - by setting a custom number format

https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68

Hide text:

Sub HideInfo()
    With ActiveSheet.PivotTables("PivotTable1")

        HideText .PivotFields("Name").DataRange
        HideText .PivotFields("ID").DataRange

    End With
End Sub

Unhide text:

Sub ShowInfo
    With ActiveSheet.PivotTables("PivotTable1")

        HideText .PivotFields("Name").DataRange, False   '<<edited
        HideText .PivotFields("ID").DataRange, False     '<<edited

    End With
End Sub

Utility sub for toggling cell format to hide/unhide content:

Sub HideText(rng as Range, Optional bHide As Boolean = True)
    rng.NumberFormat = IIf(bHide, ";;;""""", "General")
End Sub

Note you could use a format as part of a CF rule format, if you only needed to hide certain values. This has the advantage that it is dynamic and so would change with the data...

edited Oct 19 '18 at 05:39

answered Oct 17 '18 at 21:38

Tim Williams

154,628
8
97
125

unfortunately, nothing works now, I have no idea why.. I constantly get 1004 error, though I triple checked the columns' names and tried to plug source name and custom name - nothing helps.. – Tart Oct 18 '18 at 18:35
HideText and sub for toggling also doesn't work, get "Sub or Function not defined" error. – Tart Oct 18 '18 at 18:44
HideText with False shoots "Expected: end of statement" compile error – Tart Oct 18 '18 at 19:05
I was missing commas before the `False` in the unhide lines, but otherwise tests out fine for me. – Tim Williams Oct 18 '18 at 19:54
I'm completely stuck here.. Could you explain a bit more how I can use "HideText" option without Sub/End Sub? It always opens a window where I should select a macro but I don't have any... Also do you have any ideas why I constantly get 1004 error if I try to run your previous option `.PivotFields("Name").DataRange.NumberFormat = ";;;"""""` ? – Tart Oct 18 '18 at 20:25
Those samples of code are for you to insert where you need them - it's not my aim to write the whole thing out, just to give you some ideas. The code needs to be in a method (Sub/Function) so you will need to add those parts. – Tim Williams Oct 18 '18 at 20:29
I'm not that proficient in VBA as you are, I just started to getting into it and if I knew or had a slightest idea how to combine or insert those samples I wouldn't ask (I usually try to solve a problem on my own). I thought this community help to solve problems not to make them more complicated. Although I assume you have a final code and know how to solve this problem, I have to make another post to ask the community how I can write a full code using those samples as I cannot create the solution myself. Anyways, thanks for your help, I will continue trying to find a solution to my problem. – Tart Oct 19 '18 at 05:25
The code you posted had two full subs (one with correctly nested `With` blocks!), so I (reasonably I thought) assumed you knew how to write code to some degree. All you need to do is put (eg) `Sub HideInfo()` and `End Sub` around each block of code and you're done. – Tim Williams Oct 19 '18 at 05:30
Thank you, I understand, everyone has it's own approach. I've tried doing that multiple times before but constantly get an error 1004: "unable to get the pivotfields property of the worksheet class". I've checked names for my columns (Source and Custom names) but it seems that the code still cannot find them and I'm not sure why.. – Tart Oct 19 '18 at 13:25

VBA Change font color based on backgroud color in pivot table

1 Answers1