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]

You Have to select your range for CF starting from A2, Not A1

See again my sample in Post #2, bottom, Cells with conditional formatting, range Starts with A2

No jtakw. It is still not working as I am expecting. I inserted a row in the top and entered the formula in the new rule to format the row. But...

 

[jtakw]

This is your sample from Post #9, using my CF formula, works for me.
Tell me which row is incorrect:

Book3.xlsx
	A	B	C	D	E	F
2		January				7091
3		January				7091.2
4		February				7091.2
5		March				4534
6		March				4534.4
7		April				5940
8		April				5940.22
9		May				5940
10		June				1365
11		July				1000
12		July				1000.15
13		August				1000
14		September				999.05
15		September				1000
16		October				8500
17		October				8500
18		November				8500
19		December				929
20		December				929.25
21		Sunday				29075.5
22		Sunday				29075
23		Tuesday				1147
24		Tuesday				1147.14
25		Thursday				16996
26		Thursday				16997
27		Saturday				1859
28		Saturday				1859
29		Friday				10363.5
30		Friday				10364
31		Saturday				2514
32		January				100
33		January				100
34		February				100
35		March				99.5
36		March				100
37		April				100
38		April				100
39		May				100
40		June				100
41		July				100
42		July				100.95
Sheet799

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

 

[RAJESH1960]

This is your sample from Post #9, using my CF formula, works for me.
Tell me which row is incorrect:

Book3.xlsx
	A	B	C	D	E	F
2		January				7091
3		January				7091.2
4		February				7091.2
5		March				4534
6		March				4534.4
7		April				5940
8		April				5940.22
9		May				5940
10		June				1365
11		July				1000
12		July				1000.15
13		August				1000
14		September				999.05
15		September				1000
16		October				8500
17		October				8500
18		November				8500
19		December				929
20		December				929.25
21		Sunday				29075.5
22		Sunday				29075
23		Tuesday				1147
24		Tuesday				1147.14
25		Thursday				16996
26		Thursday				16997
27		Saturday				1859
28		Saturday				1859
29		Friday				10363.5
30		Friday				10364
31		Saturday				2514
32		January				100
33		January				100
34		February				100
35		March				99.5
36		March				100
37		April				100
38		April				100
39		May				100
40		June				100
41		July				100
42		July				100.95
Sheet799

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

I must have copied the formula wrong maybe. Please let me check what is wrong. will come back to you soon

 

[RAJESH1960]

I must have copied the formula wrong maybe. Please let me check what is wrong. will come back to you soon

Your sheet is perfectly right. But when I am entering the same formula I am getting some wrong matches, like the fourth row is not a match, the 3rd row is a match. but it is showing the opposite. In many other rows, there is the same error.

Query Approximate match.xlsx
	A	B	C	D	E	F
1
2		January				7091
3		January				7091.2
4		February				7091.2
5		March				4534
6		March				4534.4
7		April				5940
8		April				5940.22
9		May				5940
10		June				1365
11		July				1000
12		July				1000.15
13		August				1000
14		September				999.05
15		September				1000
16		October				8500
17		October				8500
18		November				8500
19		December				929
20		December				929.25
21		Sunday				29075.5
22		Sunday				29075
23		Tuesday				1147
24		Tuesday				1147.14
25		Thursday				16996
26		Thursday				16997
27		Saturday				1859
28		Saturday				1859
29		Friday				10363.5
30		Friday				10364
31		Saturday				2514
32		January				100
33		January				100
34		February				100
35		March				99.5
36		March				100
37		April				100
38		April				100
39		May				100
40		June				100
41		July				100
42		July				100.95
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:F	Expression	=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))	text	NO

 

[jtakw]

You CF formatted A:F, which includes the First row, as I said, You Have to start with A2.

Select your range, Starting with A2.

 

[RAJESH1960]

You CF formatted A:F, which includes the First row, as I said, You Have to start with A2.

Select your range, Starting with A2.

I tried using the range without freezing but once I press apply it auto locks the cell.
=$A$2:$F$42

 

[jtakw]

That shouldn't be a problem at all, as long as it starts with A2.

 

[RAJESH1960]

That shouldn't be a problem at all, as long as it starts with A2.

But I am not getting the perfect result you got. I am getting lot of wrong mismatches.

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
A:F	Expression	=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))	text	NO

even after entering A2 in place of A i am getting the same result

 

[RAJESH1960]

But I am not getting the perfect result you got. I am getting lot of wrong mismatches.

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
A:F	Expression	=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))	text	NO

even after entering A2 in place of A i am getting the same result

Can you tell me How can I copy or download your sheet.

 

[jtakw]

Look, you're Still using A:F