Forum Discussion

jjmthena2025's avatar
jjmthena2025
Copper Contributor
Jan 20, 2023

CONDITIONAL FORMATTING - EXCLUDE/SKIP TEXT

Hey all,

 

Hope this issue isn't too complicated. I have a worksheet that lists dates with rules that highlight them in various colors depending on how far away or past they are from today's date. These rules work great with no problem whatsoever. 

 

I also have a rule that ensures no formatting is applied to cells with "blank" values in the subject columns. (This would change when dates were filled into those cells.)

 

The last rule I wanted to add would ensure text entered into any cells in those columns would not be formatted either (no highlight). This is the struggle. I've tried ISNUMBER formulae with NOT and ISTEXT, along with checking "Stop If True" boxes to force prioritization. Can someone help with this? How can I ensure all cells containing text within the column do not highlight as if they are dates?

 

Snippet:

 

 

5 Replies

  • jjmthena2025 

    =OR(ISTEXT($H1),ISTEXT($I1))

    You can try this rule for conditional formatting. Instead of "No format set" i've selected format white color.

    • jjmthena2025's avatar
      jjmthena2025
      Copper Contributor

      OliverScheurich Thanks for replying. This would work, but I'm trying to ensure any row in the columns can be typed in with text, not just the first. Plus, when we type in those comments, we may need to manually color those as well. That condition would keep them white. 

      But it looks like it didn't work for me here. Maybe there's a conflict between conditions? 

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        jjmthena2025 

        You are welcome. Isn't it possible to enter text in any row of the columns H and I?

         

        For rule "cells contains blank value" there is no format set and the OR( rule is in quotation marks which might cause a problem. Maybe you can attach a file without sensitive data. This would simplify the task.

         

         

        If you conditionally format cells containing text white (background color) you can manually format their font color as shown in the screenshot.

         

         

Resources