Conditional Formatting not working properly with AND function

[Cliffork]

Hello,

I'm trying to create a quick check for a process document I use in my lab to ensure serial numbers are being applied in 4 places and they all have the correct serial number. Two are entered manually (row 1/2) and two are pulled from other sheets (row 3/4). I have the conditional formatting set to turn the fill color green if they all match, or remain yellow if they aren't all filled in yet. For some reason though, even though the formula is pulling TRUE, it is only formatting one cell green and I cannot for the life of me figure out why it is acting that way. Any suggestions?

New Envision Compilation -Template-.xlsx
	A	B	C	D	E
68	Back Housing			2408301B
69	Bluetooth			2408301B
70	Eeproms			2408301B
71	Paperwork			2408301B
Final Inspection Re-Check

Cell Formulas
Range	Formula
D70	D70	=RIGHT('2nd Values Capture'!A7,LEN('2nd Values Capture'!A7)-13)
D71	D71	='Cal Form'!K8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D68:E71	Expression	=AND(D68=D69,D69=D70,D70=D71)	text	NO
D68:E70	Cell Value	=0	text	YES
D71:E71	Cell Value	=0	text	YES
D68:E71	Cell	contains an error	text	NO

 

[Joe4]

Since you are trying to apply it across all four rows and across two columns, you would need to lock down the range references in your formulas, i.e.

=AND($D$68=$D$69,$D$69=$D$70,$D$70=$D$71)

 

[Cliffork]

I also want to make a point that my other formatting isn't affecting this. I recreated it on fresh cells and just typed a in all three with the same result. Maybe I just need a suggestion on a different way to do this altogether?

New Envision Compilation -Template-.xlsx
	K	L
69	a	TRUE
70	a
71	a
Final Inspection Re-Check

Cell Formulas
Range	Formula
L69	L69	=AND(K69=K70,K70=K71)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K69:K71	Expression	=AND(K69=K70,K70=K71)	text	NO

 

[Cliffork]

Since you are trying to apply it across all four rows and across two columns, you would need to lock down the range references in your formulas, i.e.

=AND($D$68=$D$69,$D$69=$D$70,$D$70=$D$71)

This worked! I do not understand what that did at all, but it did work. What does adding the $ actually change?
I did try something similar earlier since I'm using merged cells, but it reacts the same way on a fresh set of 3 cells like I showed in my other comment.

 

[Joe4]

It locks down the range reference it is before (the row or column).

For example, if you selected cells K69:K71, and then enter in the formula:

=L69

Excel will check cell K69 to see if it is the same as cell L69.
For your other two cells in your selected range, it will automatically change the formula to flow with the range, so that:
cell K70 will be compared to the value in L70 and
cell K71 will be compared to the value in L71.

However, what if we wanted ALL three cells to be compared to just L69?
Then to keep the row portion of our formula from "floating" as we move down rows, we need to lock the row portion like this:

=L$69

For a deeper explanation, see here: Switch between relative and absolute references - Microsoft Support.