Conditional Formatting won't allow font color changes but will allow fill color changes

[uptopjimbo]

Hello all, 1st post here so I will try and be as descriptive as possible. Appreciate any help!

I'm using Excel 2018 and I'm trying to get conditional formatting to show an arrow pointing up in green and an arrow pointing down in red. I do not want to fill the cell with color, I only want the font to change color. My workbook is not shared and I only have 1 worksheet so it is impossible to have other cells selected.

The formula I'm using is =IF(LINEST(I21:K21)>0,"p","q") where my font is wingdings3 so "p" is up arrow and "q" is down arrow. Excel will allow me to use conditional formatting to change the cell color as shown below:


The issue is that I don't want a green filled cell. I want my arrow to be green. When I go to do the same formula in conditional formatting (ie. =$M$21="p") and change the font color instead of the cell color this is what excel shows me and each time I change the color to green it defaults back to black (below).

https://drive.google.com/file/d/1YyDyMKo6YM0SQBg_xWSpApikJrXTY8IY/view


I've tried removing all conditional formats and starting from scratch. I've even opened new blank workbooks and started from scratch and same thing. Thanks again for any help. Much appreciated!

 

[James006]

Hello,

Not sure to understand your obstacles ...

As long as within your cell M21, you have displayed either p or q ... and you have formatted your cell with Wingdings 3 ...

You two conditional formatting rules (one for each letter) will allow you to select the Font color ... which will produce a Green or a Red Arrow ...

 

[uptopjimbo]

Hi James006,

I don't understand what you're trying to say. I'm trying to conditionally format cell M21. I am using wingdings3 as a font which really doesn't play into this at all. If I used Arial it is the same problem. And yes, if I find a way to get the conditional formatting for "p" to work then I will create an additional conditional format in the same cell for "q".

 

[James006]

Let me try to clarify ...

1. In cell M21 formatted with Wingdings3 ... you do have either either the letter P or the letter Q

2. Then, you only need two conditional rules for the cell M21 :

= M21 = "p" with Format Font Green
and
=M21 = "q" with Format Font Red

Hope this clarifies

 

[uptopjimbo]

Thanks for clarification. I understood what you were saying and what I was trying to say is that what you are recommending is not accurate. That can be seen in my original post where I showed that excel allows the conditional formatting for the cell fill based on the EXACT same parameters (i.e. "p"). Regardless of the text being wingdings, arial, times new roman, or any other variation thereof, excel knows that that arrow is "p".

To me this issue has a deeper problem. I'm usually pretty good at figuring these things out so I thought I'd try an excel board for the first time, but I think I've stumped the board and myself.

Thank you anyway for trying. Much appreciated.

 

[Fluff]

As long as the applies to range is M21 then the formatting should work.
Have you tried repairing Excel?

 

[uptopjimbo]

So....I just opened this file on a co-workers computer, same version of excel and it works just fine. I think Excel needs reparied. I'm going to try that and let everyone know. Thanks again.

 

[uptopjimbo]

So after talking with our IT guy it looks like my computer had a registry error that was causing issues. I first tried the "repair" option and that didn't work. If it's a registry error you experience then you're going to need full blown uninstall, eliminate all MS OFFICE, and then do a fresh install. Once I did that I opened my file up and conditional formatting worked perfectly.

Thanks again for all the comments. Much appreciated.

 

[Fluff]

Glad it's sorted & thanks for the feedback