Conditional Formatting Hides Text

Question

I have an Excel worksheet which I'm adding conditional formatting to from an add-in written in C#.

The condition fires ok and I'm able to change the fill colour but the text always gets hidden.

If I remove the fill colour from the format, the text still gets hidden when the formatting is applied.

If I remove all the formatting and just apply the condition without any format changes, the text is still hidden.

When the format condition is no longer valid, the text appears as you would expect.

The cell value is definitely set correctly.

Why would this happen?

C# code as follows:

var disabledFormat = "IF(blah blah...),FALSE,TRUE)";
var formatCondition = (Excel.FormatCondition)cell.FormatConditions.Add(
                           Excel.XlFormatConditionType.xlExpression, 
                           Type.Missing, disabledFormat);
formatCondition.Font.Color = ColorTranslator.FromHtml("#C0C0C0");
formatCondition.Interior.Color = ColorTranslator.FromHtml("#F0F0F0");

[Edit]

I have tried changing the colour to white, black, red etc. but it is still invisible when the conditional formatting applies.

[/Edit]

[Edit2]

Full disabledFormat string as requested: (The code does not look exactly link this as it spans several classes, I've just tried to fill-in the blanks to be helpful :$)

const string FORMAT_DISABLED = "=IF(LEFT(MID('{2}'!{0},FIND(\"|\",'{2}'!{0},FIND(\"|\",'{2}'!{0})+1)+1,999),LEN(INDIRECT(\"'$lookup_grading'!\"&ADDRESS({1},1))))=INDIRECT(\"'$lookup_grading'!\"&ADDRESS({1},1)),FALSE,TRUE)";

var dropdownCell = "Q5";

var disabledFormat = string.Format(FORMAT_DISABLED, cellName, dropdownCell, controlSheetName);

Resolves to:

=IF(LEFT(MID('$controls_Distribution Grid'!W19,FIND("|",'$controls_Distribution Grid'!W19,FIND("|",'$controls_Distribution Grid'!W19)+1)+1,999),LEN(INDIRECT("'$lookup_grading'!"&ADDRESS(Q5,1))))=INDIRECT("'$lookup_grading'!"&ADDRESS(Q5,1)),FALSE,TRUE)

To try to clarify further, what this does is it looks up a value in a cell in another worksheet with the same address, grabs a value from a formatted string in that cell and compares it to the value indicated by the selected item in a dropdown. If there is a match TRUE is returned.

The same formula is also used to return 1 or 0 for the cell value so I know this works ok.

[/Edit2]

[Edit3]

I've narrowed the problem down to the NumberFormat, which is "a";;;. When the conditional formatting does not trigger this correctly shows a (or a tick with Webdings applied). But when the conditional formatting triggers the output from the NumberFormat does not appear. I can't think why this would be intentional so I'm guessing this is a bug in Excel, but I'll do some Googling to check

[/Edit3]

This is the formula in the cell which returns a 1 or 0 (hope it's clear enough!):

=IF(
    LEFT(
        MID('$controls_Distribution Grid'!$V$19,
            FIND("|",'$controls_Distribution Grid'!$V$19,
                FIND("|",'$controls_Distribution Grid'!$V$19)+1
            )+1,999),
        LEN(INDIRECT("'$lookup_grading'!"&ADDRESS(Q5,1)))
    )=INDIRECT("'$lookup_grading'!"&ADDRESS(Q5,1)),
1,0)

Are you sure it's hidden and not just the same colour? (or a very similar colour that makes it look hidden) — musefan
No, either could be true. But setting the font colour to anything makes no difference. If I over-type the cell I can see the text while typing but it disappears when I leave the cell. — CompanyDroneFromSector7G
I am 100% sure that the font colour is not the same. The problem still occurs if I don't change any colours. — CompanyDroneFromSector7G
Can you show the actual disabledFormat string? And are those the only 2 formatConditions you have? — chancea
I'm not sure how helpful it will be but I've supplied the code to construct the disabledFormat string, plus an example of how it looks when constructed. — CompanyDroneFromSector7G

am05mhz am05mhz · Accepted Answer · 2015-02-12T10:38:54

try using this

formatCondition.Font.ThemeColor = ColorTranslator.FromHtml("#909090");
formatCondition.Interior.ThemeColor = ColorTranslator.FromHtml("#F0F0F0");
formatCondition.Interior.PatternColorIndex = xlAutomatic;

I don't know if the PatternColorIndex is necessary or not, that is what I got from macro recording.

Edit: this is the macro if that helps you:

With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.499984740745262
End With
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.149998474074526
    .PatternTintAndShade = 0
End With

Conditional Formatting Hides Text

3 Answers