conditional formatting the whole row

[RAJESH1960]

Hello
This is a question solved earlier by etaf. But I am getting some problem. I need help to correct the formula as it is not working in some cases. Below is the image where the formula should match the condition. If the names in the B column are same one below the other and the amounts too are same with a maximum difference of 1/- more or less, then the rows shouldn't be formatted. Else it should be formatted
The top row may be less also then the below row. Whatever the case the difference between the two amounts is 1/- or less then it should show matched.

Query Approximate match.xlsx
	A	B	C	D	E	F	G	H
1		January				7091.00		This has matched - difference is not more than 1/- or less and is correct
2		January				7091.20		This has matched - difference is not more than 1/- or less and is correct
3		February				7091.20
4		March				4534.00
5		March				4534.40
6		April				5940.00		This has matched - difference is not more than 1/- or less and is correct
7		April				5940.22		This has matched - difference is not more than 1/- or less and is correct
8		May				5940.00
9		June				1365.00
10		July				1000.00		=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1
11		July				1000.15		=$A:$F
12		August				1000.00
13		September				999.05		Matched but not accepted
14		September				1000.00		Matched but not accepted
15		October				8500.00
16		October				8500.00
17		November				8500.00
18		December				929.00
19		December				929.25
20		Sunday				29075.50		Matched but not accepted
21		Sunday				29075.00		Matched but not accepted
22		Tuesday				1147.00
23		Tuesday				1147.14
24		Thursday				16996.00		Matched but not accepted
25		Thursday				16997.00		Matched but not accepted
26		Saturday				1859.00
27		Saturday				1859.00
28		Friday				10363.50		Matched but not accepted
29		Friday				10364.00		Matched but not accepted
30		Saturday				2514.00
31		January				100.00
32		January				100.00
33		February				100.00
34		March				99.50		Matched but not accepted
35		March				100.00		Matched but not accepted
36		April				100.00
37		April				100.00
38		May				100.00
39		June				100.00
40		July				100.00		Matched but not accepted
41		July				100.95		Matched but not accepted
42		August				100.00
43		September				100.00		Matched but not accepted
44		September				99.00		Matched but not accepted
45		October				99.50
46		October				100.50		Matched but not accepted
47		November				100.00		Matched but not accepted
48		December				100.00
49		December				100.00
50		Sunday				100.00
51		Sunday				100.00
format rows 2 conditions

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F28:F29	Expression	=COUNTIFS($B$1:$B$4000,$B28,$F$1:$F$4000,"<"&$F28+0.5,$F$1:$F$4000,">"&$F28-0.5)=1	text	NO
A1:F27,A28:E29,A30:F51	Expression	=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1	text	NO
A1:F27,A28:E29,A30:F51	Expression	=COUNTIFS($B$1:$B$4000,$B1048417,$F$1:$F$4000,"<"&$F1048417+0.5,$F$1:$F$4000,">"&$F1048417-0.5)=1	text	NO

 

[RAJESH1960]

Look, you're Still using A:F

Query Approximate match.xlsx
	A	B	C	D	E	F
1
2		January				7091.00
3		January				7091.20
4		February				7091.20
5		March				4534.00
6		March				4534.40
7		April				5940.00
8		April				5940.22
9		May				5940.00
10		June				1365.00
11		July				1000.00
12		July				1000.15
13		August				1000.00
14		September				999.05
15		September				1000.00
16		October				8500.00
17		October				8500.00
18		November				8500.00
19		December				929.00
20		December				929.25
21		Sunday				29075.50
22		Sunday				29075.00
23		Tuesday				1147.00
24		Tuesday				1147.14
25		Thursday				16996.00
26		Thursday				16997.00
27		Saturday				1859.00
28		Saturday				1859.00
29		Friday				10363.50
30		Friday				10364.00
31		Saturday				2514.00
32		January				100.00
33		January				100.00
34		February				100.00
35		March				99.50
36		March				100.00
37		April				100.00
38		April				100.00
39		May				100.00
40		June				100.00
41		July				100.00
42		July				100.95
new

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F42	Expression	=NOT(OR(AND($B3=$B2,ABS($F3-$F2)<=1),AND($B3=$B4,ABS($F3-$F4)<=1)))	text	NO

 

[jtakw]

Now you're using the Wrong formula, it should be:

=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))

NOT what you have in post #21.

 

[RAJESH1960]

Query Approximate match.xlsx
	A	B	C	D	E	F
1
2		January				7091.00
3		January				7091.20
4		February				7091.20
5		March				4534.00
6		March				4534.40
7		April				5940.00
8		April				5940.22
9		May				5940.00
10		June				1365.00
11		July				1000.00
12		July				1000.15
13		August				1000.00
14		September				999.05
15		September				1000.00
16		October				8500.00
17		October				8500.00
18		November				8500.00
19		December				929.00
20		December				929.25
21		Sunday				29075.50
22		Sunday				29075.00
23		Tuesday				1147.00
24		Tuesday				1147.14
25		Thursday				16996.00
26		Thursday				16997.00
27		Saturday				1859.00
28		Saturday				1859.00
29		Friday				10363.50
30		Friday				10364.00
31		Saturday				2514.00
32		January				100.00
33		January				100.00
34		February				100.00
35		March				99.50
36		March				100.00
37		April				100.00
38		April				100.00
39		May				100.00
40		June				100.00
41		July				100.00
42		July				100.95
new

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F42	Expression	=NOT(OR(AND($B3=$B2,ABS($F3-$F2)<=1),AND($B3=$B4,ABS($F3-$F4)<=1)))	text	NO

Now you're using the Wrong formula, it should be:

=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))

NOT what you have in post #21.

Phew' Finally got it. I was selecting the whole column and applying the conditional formatting whereas I had to select from F1 to the end of the last row. Thanks a ton jtakw for your help and patience

 

[jtakw]

You're welcome, glad you got it working.