CONDITIONAL FORMATTING - EXCLUDE/SKIP TEXT

Copper Contributor

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:

jjmthena2025_0-1674212168151.png

 

 

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.

conditional fromatting.JPG

@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? 

jjmthena2025_0-1674215834720.png

 

@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.

Quadruple_Pawn_0-1674218162301.jpeg

 

 

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

Quadruple_Pawn_1-1674218162477.jpeg

 

 

@OliverScheurich I can enter text in any row; however, I want to have the rows with text remain white or unaffected by the conditions causing the cells with dates to change color. As of now, they change color to green even if there's just one letter typed in.

 

Okay, So I should try removing the quotes? And coloring the cells themselves is preferable to coloring the letters. I'll try attaching the file too. 

 

Update: Looks like the xlsx/xlsb file types aren't supported here. Can't attach the file.

@jjmthena2025 

Attached is my sample file. Maybe this is helpful. As far as i know it isn't possible to manually format the background color of a cell if it's (background color is) already formatted by conditional formatting.

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

This is the rule for conditional formatting if a cell is blank. The chosen format is white. The rules have to be entered without quotation marks as shown in the screenshot. Otherwise they aren't recognised and don't work.

format chosen is white.JPG