Conditional formatting

[RAJESH1960]

Hi, I have already asked this question before and was answered by etaf, but I have one more correction to be made. So I am posting again .
I have figures in column F. I want to color the whole row if the value is unique, i.e., if it is not repeated. If the difference is less than 1 then too it should color the row. The second condition is that the values / text in column B should be the same.

Book4
	B	C	D	E	F
1	A				87091.00
2	A				87091.20
3	B				7040.00
4	C				114534.00
5	C				114534.40
6	D				275940.00
7	D				275940.22
8	E				13650.00
9	E				13650.50
10	F				33264.00
11	F				33264.00
12	G				34020.00
13	H				39060.00
14	H				39060.67
15	I				68250.00
16	I				68250.00
17	Z				68250.00
18	J				929.00
19	J				929.25
20	K				1147.00
21	K				1147.00
22	L				1549.00
23	L				1549.44
24	M				1549.00
25	M				1549.00
26	N				1859.00
27	N				1859.00
28	O				2514.00
29	O				2514.00
30	P				2514.00
31	P				2514.14
32	Q				4026.00
33	Q				4026.00
34	R				4956.00
35	R				4956.00
Sheet1

 

[etaf]

OH OK, was not expecting that , as its only testing .5 between each number
so lets say we have
7090 & 7090.2 & 7091.3 & 7091.9 for january
Then 7091.9 will highlight as its not + or - 0.5 from the others

But I think you DO NOT want it highlighted if 1 difference

Book6
	A	B	C	D	E	F
1		January				7091
2		January				7091.2
3		January				7091.3
4		january				7091.9
5		March				4534.4
6		April				5940
7		April				5940.22
8		May				5940
9		June				1365
10		July				1000
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:F30	Expression	=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1	text	NO

 

[RAJESH1960]

etaf. It's Perfect. I forgot to color May. My mistake. Thanks once again etaf.

 

[RAJESH1960]

etaf. If I try to change the range to 4000 rows in the formulas, I am not getting the expected. Will share the sheet again tomorrow. Time to hit the sack.

 

[etaf]

yes the range needs to change to support your range of rows
=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1
change the range
=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1
OR increase to more rows - just depends on data
you could just reference the complete column BUT that is not very efficient for excel
=COUNTIFS($B:$B,$B1,$F:$F,"<"&$F1+0.5,$F:$F,">"&$F1-0.5)=1

 

[RAJESH1960]

yes the range needs to change to support your range of rows
=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1
change the range
=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1
OR increase to more rows - just depends on data
you could just reference the complete column BUT that is not very efficient for excel
=COUNTIFS($B:$B,$B1,$F:$F,"<"&$F1+0.5,$F:$F,">"&$F1-0.5)=1

Just copy paste the same data about 10 times below the last empty row and check. The above cells which were colored are also effected.

 

[etaf]

Just copy paste the same data about 10 times below the last empty row and check. The above cells which were colored are also effected.

sorry dont understand

 

[RAJESH1960]

sorry dont understand

Your formula works in a fixed data sheet, but if I change the date or add additional rows to the data the formula doesn't work. I think I will go with this formula until I am able to explain you better again next time. Till then this will do. Thanks etaf.
=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1