conditional formatting the whole row

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGH
1January7091.00This has matched - difference is not more than 1/- or less and is correct
2January7091.20This has matched - difference is not more than 1/- or less and is correct
3February7091.20
4March4534.00
5March4534.40
6April5940.00This has matched - difference is not more than 1/- or less and is correct
7April5940.22This has matched - difference is not more than 1/- or less and is correct
8May5940.00
9June1365.00
10July1000.00=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1
11July1000.15=$A:$F
12August1000.00
13September999.05Matched but not accepted
14September1000.00Matched but not accepted
15October8500.00
16October8500.00
17November8500.00
18December929.00
19December929.25
20Sunday29075.50Matched but not accepted
21Sunday29075.00Matched but not accepted
22Tuesday1147.00
23Tuesday1147.14
24Thursday16996.00Matched but not accepted
25Thursday16997.00Matched but not accepted
26Saturday1859.00
27Saturday1859.00
28Friday10363.50Matched but not accepted
29Friday10364.00Matched but not accepted
30Saturday2514.00
31January100.00
32January100.00
33February100.00
34March99.50Matched but not accepted
35March100.00Matched but not accepted
36April100.00
37April100.00
38May100.00
39June100.00
40July100.00Matched but not accepted
41July100.95Matched but not accepted
42August100.00
43September100.00Matched but not accepted
44September99.00Matched but not accepted
45October99.50
46October100.50Matched but not accepted
47November100.00Matched but not accepted
48December100.00
49December100.00
50Sunday100.00
51Sunday100.00
format rows 2 conditions
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F28:F29Expression=COUNTIFS($B$1:$B$4000,$B28,$F$1:$F$4000,"<"&$F28+0.5,$F$1:$F$4000,">"&$F28-0.5)=1textNO
A1:F27,A28:E29,A30:F51Expression=COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1textNO
A1:F27,A28:E29,A30:F51Expression=COUNTIFS($B$1:$B$4000,$B1048417,$F$1:$F$4000,"<"&$F1048417+0.5,$F$1:$F$4000,">"&$F1048417-0.5)=1textNO
 
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,848
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFG
1
2January7091FALSE
3January7091.2FALSE
4February7091.2TRUE
5March4534FALSE
6March4534.4FALSE
7April5940FALSE
8April5940.22FALSE
9May5940TRUE
10June1365TRUE
11July1000FALSE
12July1000.15FALSE
13August1000TRUE
14September999.05FALSE
15September1000FALSE
16October8500FALSE
17October8500FALSE
18November8500TRUE
19December929FALSE
20December929.25FALSE
21Sunday29075.5FALSE
22Sunday29075FALSE
23Tuesday1147FALSE
24Tuesday1147.14FALSE
25Thursday16996FALSE
26Thursday16997FALSE
27Saturday1859FALSE
28Saturday1859FALSE
29Friday10363.5FALSE
30Friday10364FALSE
31Saturday2514TRUE
32January100FALSE
33January100FALSE
34February100TRUE
35March99.5FALSE
36March100FALSE
37April100FALSE
38April100FALSE
39May100TRUE
40June100TRUE
41July100FALSE
42July100.95FALSE
43August100TRUE
44September100FALSE
45September99FALSE
46October99.5FALSE
47October100.5FALSE
48November100TRUE
49December100FALSE
50December100FALSE
51Sunday100FALSE
52Sunday100FALSE
Sheet797
Cell Formulas
RangeFormula
G2:G52G2=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:F52Expression=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS
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
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

have you tried

jtakw

reply, may be better then i produced
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFG
1
2January7091FALSE
3January7091.2FALSE
4February7091.2TRUE
5March4534FALSE
6March4534.4FALSE
7April5940FALSE
8April5940.22FALSE
9May5940TRUE
10June1365TRUE
11July1000FALSE
12July1000.15FALSE
13August1000TRUE
14September999.05FALSE
15September1000FALSE
16October8500FALSE
17October8500FALSE
18November8500TRUE
19December929FALSE
20December929.25FALSE
21Sunday29075.5FALSE
22Sunday29075FALSE
23Tuesday1147FALSE
24Tuesday1147.14FALSE
25Thursday16996FALSE
26Thursday16997FALSE
27Saturday1859FALSE
28Saturday1859FALSE
29Friday10363.5FALSE
30Friday10364FALSE
31Saturday2514TRUE
32January100FALSE
33January100FALSE
34February100TRUE
35March99.5FALSE
36March100FALSE
37April100FALSE
38April100FALSE
39May100TRUE
40June100TRUE
41July100FALSE
42July100.95FALSE
43August100TRUE
44September100FALSE
45September99FALSE
46October99.5FALSE
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]
48November100TRUE
49December100FALSE
50December100FALSE
51Sunday100FALSE
52Sunday100FALSE
Sheet797
[XH]
Cell Formulas
RangeFormula
G2:G52G2=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:F52Expression=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))textNO
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

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,848
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFG
1
2January7091Match
3January7091.2Match
4February7091.2
5March4534Match
6March4534.4Match
7April5940
8April5940.22
9May5940
10June1365
11July1000Match
12July1000.15Match
13August1000
14September999.05Match
15September1000Match
16October8500Match
17October8500Match
18November8500
19December929Match
20December929.25Match
21Sunday29075.5Match
22Sunday29075Match
23Tuesday1147Match
24Tuesday1147.14Match
25Thursday16996Match
26Thursday16997Match
27Saturday1859Match
28Saturday1859Match
29Friday10363.5Match
30Friday10364Match
31Saturday2514
32January100Match
33January100Match
34February100
35March99.5Match
36March100Match
37April100Match
38April100Match
39May100
40June100
41July100Match
42July100.95Match
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:F40,A43:F1048563Expression=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))textNO
A42:F42Expression=NOT(OR(AND(#REF!=$B42,ABS(#REF!-$F42)<=1),AND(#REF!=#REF!,ABS(#REF!-#REF!)<=1)))textNO
A41:F41Expression=NOT(OR(AND($B42=$B41,ABS($F42-$F41)<=1),AND($B42=#REF!,ABS($F42-#REF!)<=1)))textNO

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,848
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top