Conditional Formatting Problem

[Cliffork]

I'm having a bit of a problem with conditionally formatting a few cells based on 2 points of data. If neither is filled in at all, I want this cell to remain blank. If one of the two is filled in, I want it to turn red to show me that the other one wasn't completed. If both are filled in I want it to be green. My problem in the case below is that for some reason, the first conditional rule is still taking over the formatting when it is false, and showing blank. If I delete this rule, the red and green portions function correctly, but the one to clear it messes the whole thing up.

If the fact that this jumps around to a different pages confuses things I can remake it on one page, but I'll wait and see first.

Meter Repair Compilation File -Template.xlsm
	I	J	K	L
10	New Serial #	On Pre-estimate?
11	0
Checklist

Cell Formulas
Range	Formula
I10:I11	I10	='Final Insp.'!D80
K11	K11	=IF(ISNUMBER('Pre-Estimate'!D16),"True","False")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K11	Expression	=AND(LEN(I11<5),K11="False")	text	NO
K11:K14	Cell Value	contains "false"	text	NO
K11:K14	Cell Value	contains "true"	text	NO

 

[Joe4]

I am not sure I understand - K11 is a formula that returns a "True" or "False", so it cannot ever be blank.

And what formatting does your first Conditional Formatting Rule (the expression one) apply?

 

[Cliffork]

I am not sure I understand - K11 is a formula that returns a "True" or "False", so it cannot ever be blank.

And what formatting does your first Conditional Formatting Rule (the expression one) apply?

Sorry I guess it didn't include that part of the formatting in the xl2bb code.
The formatting makes it blank. It says if I11 has less that 5 characters, AND K11 is false (which just means a cell on the pre-estimate page is left blank) that it formats the cell as ;;; , which just makes the cell text invisible.

My desire was to make it so that if either the serial number is filled in(always more than 5 digits) or the cell is not blank on the pre-estimate page, that this conditional formatting would stop taking place, but it doesn't until both things are false. It almost seems to be working as an OR instead of as AND.

 

[Joe4]

I think it is because it looks like you have an error in your expression.
Instead of:

=AND(LEN(I11<5),K11="False")

it should be:

=AND(LEN(I11)<5,K11="False")

 

[DanteAmor]

In addition to fixing the parenthesis in the formula.
The first rule must have Stop = true
Also check the range Applies to:

varios 17oct2024.xlsm
	I	J	K
10	New Serial #	On Pre-estimate?
11	ab		True
12	abcdef		True
13	ab		False
14	abcdef		False
Checklist

Cell Formulas
Range	Formula
I10	I10	='Final Insp.'!D80

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K11:K14	Expression	=AND(LEN(I11)<5,K11="False")	text	YES
K11:K14	Expression	=K11="False"	text	NO
K11:K14	Expression	=K11="True"	text	NO

 

[Cliffork]

In addition to fixing the parenthesis in the formula.
The first rule must have Stop = true
Also check the range Applies to:

varios 17oct2024.xlsm
	I	J	K
10	New Serial #	On Pre-estimate?
11	ab		True
12	abcdef		True
13	ab		False
14	abcdef		False
Checklist

Cell Formulas
Range	Formula
I10	I10	='Final Insp.'!D80

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K11:K14	Expression	=AND(LEN(I11)<5,K11="False")	text	YES
K11:K14	Expression	=K11="False"	text	NO
K11:K14	Expression	=K11="True"	text	NO

I never understood what stop if true did before until I just looked it up. I think it was working without it, but I'm going to leave it checked anyways.

The range will apply to all the other cells, I was just working on this one first before applying it to the rest that have the color ones already.

 

[Cliffork]

I think it is because it looks like you have an error in your expression.
Instead of:

=AND(LEN(I11<5),K11="False")

it should be:

=AND(LEN(I11)<5,K11="False")

This was it, I just didn't realize I had the end parenthesis in the wrong spot. Thanks!

 

[Joe4]

This was it, I just didn't realize I had the end parenthesis in the wrong spot. Thanks!

You are welcome.

Yes, the way it was written, it was comparing the VALUE in I11 to the number 5, and would return either TRUE or FALSE, and then taking the length of that result!

 

[DanteAmor]

I never understood what stop if true did before until I just looked it up. I think it was working without it, but I'm going to leave it checked anyways.

According to my tests, if you hold down the stop it doesn't set the color correctly:

With out:

varios 17oct2024.xlsm
	I	J	K
10	New Serial #	On Pre-estimate?
11	ab		True
12	abcdef		True
13	ab		False
14	abcdef		False
Checklist

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K11:K14	Expression	=AND(LEN(I11)<5,K11="False")	text	NO
K11:K14	Expression	=K11="False"	text	NO
K11:K14	Expression	=K11="True"	text	NO

With:

varios 17oct2024.xlsm
	I	J	K
11	ab		True
12	abcdef		True
13	ab		False
14	abcdef		False
Checklist

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K11:K14	Expression	=AND(LEN(I11)<5,K11="False")	text	YES
K11:K14	Expression	=K11="False"	text	NO
K11:K14	Expression	=K11="True"	text	NO