CODEWITHSUNDEEP
exceloverridingconditional-formatting
Jonathan Wilson
Jonathan Wilson

Reputation: 686

Conditional Formatting not overriding manual format (trying to make cell blank)

I'm trying to make a cell disappear if certain conditions are true, but I also have some formatting of individual characters that are screwing things up.

To construct the problem, open up excel, set up as follows:

A1: Hello my name is NAME [then make sure the NAME is colored in red]

B1: 1

Then set up a conditional formatting depending on B1 that formats A1 to have WHITE text (so it disappears).

You'll notice that the RED text format does not get overwritten to be white. I feel like it has something to do with the "Automatic" option for font color, but I don't know how to fix it. Any ideas?

Upvotes: 1

Views: 854

Answers (1)

user4039065
user4039065

Reputation:

While a conditional format will take precedence over a cell's font color, it does not change the font color of one or more characters or words that have been independently changed from the cell's font color.

Making the cell's contents 'invisible' is best accomplished by setting a custom number format of ;;; rather than forcing a white font on a typically white background. The ;;; will not show numbers, dates, booleans or text but error messages (e.g. #DIV/0! or #N/A) will show. This also does not depend on a white background since you are not camouflaging the cell's contents; nothing is actually displayed.

More on creating custom number formats at Number Format Code.

Upvotes: 1

Related Questions