conditional formatting the whole row

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
482
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:

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
482
Office Version
  1. 2019
Platform
  1. Windows
Look, you're Still using A:F
Query Approximate match.xlsx
ABCDEF
1
2January7091.00
3January7091.20
4February7091.20
5March4534.00
6March4534.40
7April5940.00
8April5940.22
9May5940.00
10June1365.00
11July1000.00
12July1000.15
13August1000.00
14September999.05
15September1000.00
16October8500.00
17October8500.00
18November8500.00
19December929.00
20December929.25
21Sunday29075.50
22Sunday29075.00
23Tuesday1147.00
24Tuesday1147.14
25Thursday16996.00
26Thursday16997.00
27Saturday1859.00
28Saturday1859.00
29Friday10363.50
30Friday10364.00
31Saturday2514.00
32January100.00
33January100.00
34February100.00
35March99.50
36March100.00
37April100.00
38April100.00
39May100.00
40June100.00
41July100.00
42July100.95
new
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:F42Expression=NOT(OR(AND($B3=$B2,ABS($F3-$F2)<=1),AND($B3=$B4,ABS($F3-$F4)<=1)))textNO
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Now you're using the Wrong formula, it should be:

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

NOT what you have in post #21.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
482
Office Version
  1. 2019
Platform
  1. Windows
Query Approximate match.xlsx
ABCDEF
1
2January7091.00
3January7091.20
4February7091.20
5March4534.00
6March4534.40
7April5940.00
8April5940.22
9May5940.00
10June1365.00
11July1000.00
12July1000.15
13August1000.00
14September999.05
15September1000.00
16October8500.00
17October8500.00
18November8500.00
19December929.00
20December929.25
21Sunday29075.50
22Sunday29075.00
23Tuesday1147.00
24Tuesday1147.14
25Thursday16996.00
26Thursday16997.00
27Saturday1859.00
28Saturday1859.00
29Friday10363.50
30Friday10364.00
31Saturday2514.00
32January100.00
33January100.00
34February100.00
35March99.50
36March100.00
37April100.00
38April100.00
39May100.00
40June100.00
41July100.00
42July100.95
new
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:F42Expression=NOT(OR(AND($B3=$B2,ABS($F3-$F2)<=1),AND($B3=$B4,ABS($F3-$F4)<=1)))textNO

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

Excel Formula:
=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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, glad you got it working.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,364
Messages
5,641,712
Members
417,230
Latest member
emmah44

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