Conditional formatting changes text color when it's not meant to (or is it?)

Question

Hey y'all,

I am posting this because it seems to me that Excel is behaving in a way that it shouldn't, or I'm missing something: can you tell me which it is? :p

I use conditional formatting with a formula to fill each odd row with a gray background. My formula is =MOD(ROW(A4);2)<>0, and it is the only rule applied to this cell range. When I implement this rule, I tell Excel to implement a "custom format" for the cells that will be formatted, and these are my present settings for the dialog which opened when I selected "Custom format...":

(of course, the "Fill" tab shows some light gray in "Background color", but that is not problematic, so I didn't include a screenshot of that here).

My problem is that Excel colors the text of the conditionally formatted cells black, when I would love it if, the text color being set to "Automatic", it would actually not apply any formatting to the text color (as you cannot override conditional formatting other than with code, and I don't care to learn VBA right now...)

Hence two questions:

• Is it normal for "Automatic" to be rendered as black in any case (even if I manually format a column as red)?
• If the answer to the previous question is "yes", is there a way not to set a text color so that Excel only formats the background color of the cell without impacting the formatting of its content? (that would be a dream come true, but I haven't found a way to leave "Color:" blank as "Font:", "Font style:", and "Size:" are, unfortunately...)

Thank you very much for your answers and kind advice!

Ari.

P.S.: I use Excel 2016 on a Mac.

Answer 1

Hi Ari,

Thanks for posting back.

According to your description, my understanding is that you want to keep the original color of the fonts after applying conditional formatting to the cells.

You may try the followings to check the outcome:

When applying conditional formatting, in the Format Cells dialog box>Font, Do Not Manually change the color to Automatic, instead, click the Clear button, then it will change the color to Automatic.

Then click OK to check the outcome.

Based on my test, the font color won't change to black with the steps above.

If above doesn't help, please let us know your Office version and Mac version for further troubleshooting.

Regards,

Tina

Answer 2

Hi Ari,

Thanks for posting in the forum.

I test in the latest version of Excel for Mac with the steps you mentioned, in conditional formatting, set Font Color to Automatic and Fill the Background color with color gray. And I get the same result, the texts' color in the conditionally formatted cells is black.

Also, I test the followings(without conditional formatting):

I add texts in some cells and fill the cells with different colors as background color, then select the cells, set the Font Color to Automatic. Then the texts are all set to black.

It seems that Automatic is rendered as black when we set Automatic for Font Color in Excel.

Given this, you may try the followings:

In conditional formatting, if you do not want to change text color, you can set the text color to be the same as what it used to be. In Format Cells dialog box>Font>Color>do not select Automatic, select a color which is the same as the texts' original color.

Regards,

Tina

[Updated by Tina Chen MSFT, November 7th, 4:47 AM (UTC)]

Answer 3

Hi Tina,

Thank you very much for your quick response, and for your suggestion of a workaround:

In conditional formatting, if you do not want to change text color, you can set the text color to be the same as what it used to be. In Format Cells dialog box>Font>Color>do not select Automatic, select a color which is the same as the texts' original color.

This will not, however, work in my case, as the original text color is black, but I format my columns (and therefore the text contained within) in different colors after all the conditional formatting has been applied and is here to stay. And therefore, invariably, as you have noticed, half my text stays black whereas the other half (on even rows, thus not affected by the conditional formatting) is the color I need it to be... Or I would have to create a different rule each time I want a color for some text in a column...

I seem to understand that what I need is, I fear, probably not possible for now (and this is fine, I can make do, it's simply less pretty and readable at a glance), but would there be a sort of suggestion box where I could describe Excel's present behavior, and then ask the dev team for a change of behavior for the "Automatic" color (which would honestly be much more logical, given the name...)

Best,

Ari  ;o)

Answer 4

Wow Tina, the "Clear" button (which I hadn't noticed!) did the job!!! Thank you so very much!!