Conditional Formatting incorrectly highlighting

[Meralux]

Hello, I'm struggling with a problem where my conditional formatting rules are not behaving correctly. They won't follow the rules and they'll change cells they are not attached to at all. In the pictures, both cells D3 and E3 should be green and one is red, if I delete them and start entering different numbers, sometimes it works, sometimes it does not. Sometimes if I have something entered in cell D3 and go over to H3 and enter something, it changes the color of D3 even though they are not attached to each other at all. Anyone have any advice on why its doing this?

 

[etaf]

you have 2 rules the same for different colours
So the last one will work, as you dont have a stop if true
Try setting "STOP IF TRUE"
But you may want to make the green a different rule to red

as far as I can make out - difficult to follow just images

you could use
XL2BB - see signature
Or maybe add a public copy no sensitive data, on a share like dropbox/onedrive

 

[Meralux]

They shouldn't be the same rule? One is greater than and one is less than, or does excel see that as the same rule? The data might change so I didn't want to mark stop if true. Would it work better if I left one of them as greater than and wrote a formula for the less than?

 

[etaf]

Sorry I missed that - small images
That would be fine to have the 2 formulas , 1 less and 1 greater

the less than would highlight blank cells, hence the 3rd rule

Comparing with CA3
BUT that comparison will change as its not fixed with $

to stop the blank highlighting may need a AND()
For attaching here I have used R1 as the fixed test
and so that is with $ and so does not change
$R$1
So each cell is compared with R1 and if lower RED, IF greater green

and so the range i have below is A1 to N14 - just as i say to give a sample

RULE for red is the Less than

=AND( A1 <> "" , A1 < $R$1 )
and for green
= A1 > $R$1

IF the cell = R1 , then it will also remain blank - so you would need a < = OR > = depending on what colour needed for =

To highlight the entire row
then change to

RULE for red is the Less than
=AND( $A1 <> "" , $A1 < $R$1 )
and for green
= $A1 > $R$1

But if 2 values 1 greater and 1 lower on the same row - which rule applies , would be how they are ordered and use stop if tru

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1																		12
2
3						12
4
5				1
6
7					2
8								3
9		12
10
11					14
12
13
14
15
16
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N14	Expression	=A1>$R$1	text	NO
A1:N14	Expression	=AND( A1<> "", A1<$R$1)	text	NO

 

[Meralux]

Yeah sorry about the images, the board has a very tiny file size limit and I had to shrink them to fit. I'll plug this into my sheet and give it a shot, thank you very much!

 

[etaf]

to get full row highlighted i had to use MAX and COUNTBLANK
sure there is a better way
BUT

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1								14										12
2
3						12
4
5				1
6
7					2
8								3
9		12
10
11					14
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N14	Expression	=COUNTBLANK($A1:$N1)=14	text	YES
A1:N14	Expression	=MAX($A1:$N1)>$R$1	text	NO
A1:N14	Expression	=MAX($A1:$N1)<$R$1	text	NO

 

[Meralux]

That seems to have fixed it, thank you very much for your help good sir!

 

[etaf]

you are welcome