conditional formatting the whole row

RAJESH1960

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
This is your sample from Post #9, using my CF formula, works for me.
Tell me which row is incorrect:

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

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
This is your sample from Post #9, using my CF formula, works for me.
Tell me which row is incorrect:

Book3.xlsx
ABCDEF
2January7091
3January7091.2
4February7091.2
5March4534
6March4534.4
7April5940
8April5940.22
9May5940
10June1365
11July1000
12July1000.15
13August1000
14September999.05
15September1000
16October8500
17October8500
18November8500
19December929
20December929.25
21Sunday29075.5
22Sunday29075
23Tuesday1147
24Tuesday1147.14
25Thursday16996
26Thursday16997
27Saturday1859
28Saturday1859
29Friday10363.5
30Friday10364
31Saturday2514
32January100
33January100
34February100
35March99.5
36March100
37April100
38April100
39May100
40June100
41July100
42July100.95
Sheet799
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:F42Expression=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))textNO
I must have copied the formula wrong maybe. Please let me check what is wrong. will come back to you soon
 

RAJESH1960

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
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
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
A:FExpression=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))textNO

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

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
But I am not getting the perfect result you got. I am getting lot of wrong mismatches.
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
A:FExpression=NOT(OR(AND($B2=$B1,ABS($F2-$F1)<=1),AND($B2=$B3,ABS($F2-$F3)<=1)))textNO

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Look, you're Still using A:F
 

Watch MrExcel Video

Forum statistics

Threads
1,129,505
Messages
5,636,717
Members
416,936
Latest member
Saurabh786

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