Conditional formatting referring to two cells (one of which is the conditionally formatted cell)

ncjefffl

New Member
Joined
May 5, 2010
Messages
22
I've been trying to get this to work for two days to no avail. Here's what I'm trying to do:

Conditionally format a cell so that if blank, one color, if >= a specified cell, green, and if less than the same specified cell, red. I also don't want the conditional formatting rule to fire if the cell contains text.

I can't figure out how to tell the formatting rule not to fire if there is text in the "target cell" (the cell to which the conditional formatting is applied) while formatting based on comparison to a reference cell. If I didn't need it to ignore text, I could do it.

In this example, G12 is the cell to which I'm applying the conditional formatting. G10 is the reference cell (the cell to which G12 is being compared)

[cell value] [is equal to] [=""] (this one works)
[cell value] [is greater than or equal to] [=AND(NOT(ISTEXT(G12)),$G$10)] this should turn green but doesn't work at all in this configuration
[cell value] [is less than] [=AND(NOT(ISTEXT(G12)),$G$10)] should turn yellow if G12 doesn't contain text and is less than G10. instead, anything I enter turns the cell yellow.

I've also tried this:
[cell value] [is equal to] [=""]
[cell value] [is greater than or equal to] [="$G$10,AND(NOT(ISTEXT(G12)))"]
[cell value] [is less than] [="$G$10,AND(NOT(ISTEXT(G12)))"]
this works for a blank cell, but turns the cell green if G12 contains text and yellow for anything else that's entered.

and this:
[cell value] [is equal to] [=""]
[cell value] [is equal to] [=AND(NOT(ISTEXT(G12)),G12>=$G$10)]
[cell value] [is equal to] [=AND(NOT(ISTEXT(G12)),G12<$G$10)]
this doesn't work at all (except for the first rule, and it does let me enter text without the cell turning green or yellow, but I suspect that's not because the Not(istext) part of the rules is working.

I could go go on, but since none of the variations I've tried work, there's no real point. By the way, the [brackets] are just meant to indicate the boxes in conditional formatting. I didn't enter anything in brackets.

I feel like I'm either almost on to something or profoundly misunderstand how formulas and conditional formatting work. Any help is deeply appreciated!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Try Formula is instead of Cell value is, with your formulas.
 

ncjefffl

New Member
Joined
May 5, 2010
Messages
22
Andrew, you are my new favourite person in the world. That works! Thank you!!!:biggrin:
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top