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

 

[jtakw]

Hi,

It would help if you posted the link to the other thread where you got your current CF formula so that we have the details of your requirements.

Anyway, if you can insert a dummy row at the top of your data, I believe this CF formula will do what you want:

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

Cell Formulas
Range	Formula
G2:G52	G2	=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))

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

 

[etaf]

EDIT - replied as i was researching other post and composing

The conditional formatting rules , dont make sense - you have 3 formulas, and cover different areas, - this maybe part of the issue
and not the same as my previous post, which i think you are refering to - so a little confused

Conditional formatting

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...

www.mrexcel.com

 

[RAJESH1960]

EDIT - replied as i was researching other post and composing

The conditional formatting rules , dont make sense - you have 3 formulas, and cover different areas, - this maybe part of the issue
and not the same as my previous post, which i think you are refering to - so a little confused

Conditional formatting

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...

www.mrexcel.com

It's the same post. I have changed only the amounts which I have taken from the original sheet and are not matching. You have given +0.5 and -0.5 difference between the top and bottom row. Your formula works when the difference in amount is between the top and below row. It should work vice versa, the top row may be less than below or more than below and just increase the difference to +1 and -1.

 

[etaf]

have you tried

jtakw​

reply, may be better then i produced

 

[RAJESH1960]

Hi,

It would help if you posted the link to the other thread where you got your current CF formula so that we have the details of your requirements.

Anyway, if you can insert a dummy row at the top of your data, I believe this CF formula will do what you want:

Book3.xlsx
	A	B	C	D	E	F	G
1
2		January				7091	FALSE
3		January				7091.2	FALSE
4		February				7091.2	TRUE
5		March				4534	FALSE
6		March				4534.4	FALSE
7		April				5940	FALSE
8		April				5940.22	FALSE
9		May				5940	TRUE
10		June				1365	TRUE
11		July				1000	FALSE
12		July				1000.15	FALSE
13		August				1000	TRUE
14		September				999.05	FALSE
15		September				1000	FALSE
16		October				8500	FALSE
17		October				8500	FALSE
18		November				8500	TRUE
19		December				929	FALSE
20		December				929.25	FALSE
21		Sunday				29075.5	FALSE
22		Sunday				29075	FALSE
23		Tuesday				1147	FALSE
24		Tuesday				1147.14	FALSE
25		Thursday				16996	FALSE
26		Thursday				16997	FALSE
27		Saturday				1859	FALSE
28		Saturday				1859	FALSE
29		Friday				10363.5	FALSE
30		Friday				10364	FALSE
31		Saturday				2514	TRUE
32		January				100	FALSE
33		January				100	FALSE
34		February				100	TRUE
35		March				99.5	FALSE
36		March				100	FALSE
37		April				100	FALSE
38		April				100	FALSE
39		May				100	TRUE
40		June				100	TRUE
41		July				100	FALSE
42		July				100.95	FALSE
43		August				100	TRUE
44		September				100	FALSE
45		September				99	FALSE
46		October				99.5	FALSE
47[/XHXD=ch:15][/XD][XD=h:l]October[/XD][XD][/XD][XD][/XD][XD][/XD][XD]100.5[/XD][XD=cls:fx]FALSE[/XD]
48		November				100	TRUE
49		December				100	FALSE
50		December				100	FALSE
51		Sunday				100	FALSE
52		Sunday				100	FALSE
Sheet797

[XH]

Cell Formulas
Range	Formula
G2:G52	G2	=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))

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

jtakw. Sorry. It is formatting only the rows where the amounts are not matching. The other condition is if both the amounts are a match, i.e., upto a difference of 1/- the names in column B of the same row, should also match, else it is not a match.

 

[RAJESH1960]

Conditional formatting

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...

www.mrexcel.com

Please refer this link again.
If I change the condition from 0.5 to 1 then it accepts only the amounts where the difference is 1/- whole only and not 0.5 or any other fractional difference.
Actually this is the link marked as solution.

Conditional formatting

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...

www.mrexcel.com

 

[jtakw]

Ok, I've read the description from your other thread and here, can't say that I understand 100%,
but I believe what I posted in Post #2 might do what you want, give it a try on a Test Sheet and let us know.

 

[RAJESH1960]

Conditional formatting

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...

www.mrexcel.com

Please refer this link again.
If I change the condition from 0.5 to 1 then it accepts only the amounts where the difference is 1/- whole only and not 0.5 or any other fractional difference.

Ok, I've read the description from your other thread and here, can't say that I understand 100%,
but I believe what I posted in Post #2 might do what you want, give it a try on a Test Sheet and let us know.

I did that. But as I said it is not what I asked for. The cells showing match, which are entered manually, should be as it as as they are a match and the balance blank cells, the rows should format the row with a color. Sending you the sheet I tried. Thanks for your help.

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:F40,A43:F1048563	Expression	=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))	text	NO
A42:F42	Expression	=NOT(OR(AND(#REF!=$B42,ABS(#REF!-$F42)<=1),AND(#REF!=#REF!,ABS(#REF!-#REF!)<=1)))	text	NO
A41:F41	Expression	=NOT(OR(AND($B42=$B41,ABS($F42-$F41)<=1),AND($B42=#REF!,ABS($F42-#REF!)<=1)))	text	NO

Even row 7 and 8 are a match which I forgot to mention.

 

[jtakw]

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