Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

VBA: selectively hide text in themed workbook by making text same color as fill

I'm creating a workbook from VBA and would like to hide the text in selected columns by making the text the same color as the fill in the cell. I have no problem doing this in VBA if I use standard colors but it won't work using themed colors.

I started with the old standby technique: I recorded a macro while I manually changed the text color and fill color. The result of the manual operation is exactly what I want as you'll see in cell A1 in the attached workbook. However, if you run the macro (which I've changed to operate on cell A2), you'll see the dilemma: the text is not set to the required tint to make it invisible.

Any ideas why the automated version of the manual procedure doesn't work?

BTW, setting a custom cell format of ";;;", which does hide the cell contents, won't work because I am also using filters. When I select a filter for a column containing text that was hidden using the ";;;" format, the filter doesn't see the rows that contain hidden text.
Set-font-and-fill-color-to-themed-value.
0
Scott Helmers
Asked:
Scott Helmers
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

    With Range("A2").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("A2").Font.Color = Range("A2").Interior.Color

Open in new window

Regards
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperAuthor Commented:
Interesting, thank you.

I had tried
Range("A2").Font.ThemeColor = Range("A2").Interior.ThemeColor

Open in new window

but had not tried the .Color property instead.

Your solution works.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now