Conditional formatting

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
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 should color the row. The second condition is that the values / text in column B should be the same.
Book4
BCDEF
1A87091.00
2A87091.20
3B7040.00
4C114534.00
5C114534.40
6D275940.00
7D275940.22
8E13650.00
9E13650.50
10F33264.00
11F33264.00
12G34020.00
13H39060.00
14H39060.67
15I68250.00
16I68250.00
17Z68250.00
18J929.00
19J929.25
20K1147.00
21K1147.00
22L1549.00
23L1549.44
24M1549.00
25M1549.00
26N1859.00
27N1859.00
28O2514.00
29O2514.00
30P2514.00
31P2514.14
32Q4026.00
33Q4026.00
34R4956.00
35R4956.00
Sheet1
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
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 should color the row. The second condition is that the values / text in column B should be the same.
Book4
BCDEF
1A87091.00
2A87091.20
3B7040.00
4C114534.00
5C114534.40
6D275940.00
7D275940.22
8E13650.00
9E13650.50
10F33264.00
11F33264.00
12G34020.00
13H39060.00
14H39060.67
15I68250.00
16I68250.00
17Z68250.00
18J929.00
19J929.25
20K1147.00
21K1147.00
22L1549.00
23L1549.44
24M1549.00
25M1549.00
26N1859.00
27N1859.00
28O2514.00
29O2514.00
30P2514.00
31P2514.14
32Q4026.00
33Q4026.00
34R4956.00
35R4956.00
Sheet1
Sorry, If the difference is less than 1 in column F and the value in the correspoding row are the same it should not color the row.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,659
Office Version
  1. 365
Platform
  1. MacOS
would you give some examples coloured in on your sheet

if you had say
1
2
3
4
6



Then 3 is 1 less than 4 , so the 4's and 3's would NOT highlight
BUT the 2 is 1 less then 3 and would NOT highlight
Then the 1 is 1 less then 2 and would NOThighlight

so in my example only 6 would be highlighted as nothing is 1 less then 6 and its on its own
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
would you give some examples coloured in on your sheet

if you had say
1
2
3
4
6



Then 3 is 1 less than 4 , so the 4's and 3's would NOT highlight
BUT the 2 is 1 less then 3 and would NOT highlight
Then the 1 is 1 less then 2 and would NOThighlight

so in my example only 6 would be highlighted as nothing is 1 less then 6 and its on its own
This is a reconciliation sheet. I have name in column B and the values in column F. I have colored the values of column F that do not match with the text in column B. Values in column F can be the same but the common factor to match is in column B.
Book4
ABCDEF
1January7091.00
2January7091.20
3February7091.20
4March4534.00
5March4534.40
6April5940.00
7April5940.22
8May5940.00
9June1365.00
10July1000.00
11July1000.15
12August1000.00
13September1000.15
14September1000.00
15October8500.00
16October8500.00
17November8500.00
18December929.00
19December929.25
20Sunday1147.00
21Sunday1147.12
22Tuesday1147.00
23Tuesday1147.14
24Thursday1549.00
25Thursday1549.00
26Saturday1859.00
27Saturday1859.00
28Friday2514.00
29Friday2514.00
30Saturday2514.00
format rows amount 2 conditions
 

etaf

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

ADVERTISEMENT

so the examples make sense because of the last thread you had - those days are unique , ignoring he numbers

BUT Saturday, has a few entries and is highlighted , because its the only one with Saturday and 2514

BUT January is not because the numbers are within 1

Really trying to follow - maybe say why things are NOT highlighted
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
so the examples make sense because of the last thread you had - those days are unique , ignoring he numbers

BUT Saturday, has a few entries and is highlighted , because its the only one with Saturday and 2514
As there are no static values in the sheet except column B, it is getting difficult to match. Even in column B the values may occur repeatedly, but the amounts may be different. There are more than 4000 rows to match. A formula could help to solve it in seconds. In the above image, February, June, August, November and Saturday are a no match so I have highlighted them manually.
 

etaf

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

ADVERTISEMENT

yes , i'm following , so saturday has more then one entry - BUT its the number that makes this highlight
if in your list
JANUARY - had in F 100
and the next
JANUARY - had in F 200
would they both be highlighted

Saturday has 3 entries - BUT 26&27 row has the same number and Saturday at row 30 is unique because of the number
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
yes , i'm following , so saturday has more then one entry - BUT its the number that makes this highlight
if in your list
JANUARY - had in F 100
and the next
JANUARY - had in F 200
would they both be highlighted

Saturday has 3 entries - BUT 26&27 row has the same number and Saturday at row 30 is unique because of the number
January the difference in amount is less than 1, 7091.00 & 7091.20 so it is not to be colored. If it was 7091 & 7092 then it will have to be colored as no match. Will a round up formula in the condition help.?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,659
Office Version
  1. 365
Platform
  1. MacOS
Ok, so i think i have something
BUT why is MAY not highlighted ?

I have used a tolarnace on the number of +- 0.5
BUT we can change that

so what if

7090.1 & 7091 & 7091.9
All within 1

i have used this
=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)
and so testing if 0.5 difference

BUT it did highlight MAY

Book6
ABCDEFGHI
1January70911
2January7091.21
3February7091.2Y1
4March45341
5March4534.41
6April59401
7April5940.221
8May59401
9June1365Y1
10July10001
11July1000.151
12August1000Y1
13September1000.151
14September10001
15October85002
16October85002
17November8500Y1
18December9291
19December929.251
20Sunday11471
21Sunday1147.121
22Tuesday11471
23Tuesday1147.141
24Thursday15492
25Thursday15492
26Saturday18592
27Saturday18592
28Friday25142
29Friday25142
30Saturday2514Y
Sheet1
Cell Formulas
RangeFormula
I1:I29I1=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,$F1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:F30Expression=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1textNO
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
Ok, so i think i have something
BUT why is MAY not highlighted ?

I have used a tolarnace on the number of +- 0.5
BUT we can change that

so what if

7090.1 & 7091 & 7091.9
All within 1

i have used this
=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)
and so testing if 0.5 difference

BUT it did highlight MAY

Book6
ABCDEFGHI
1January70911
2January7091.21
3February7091.2Y1
4March45341
5March4534.41
6April59401
7April5940.221
8May59401
9June1365Y1
10July10001
11July1000.151
12August1000Y1
13September1000.151
14September10001
15October85002
16October85002
17November8500Y1
18December9291
19December929.251
20Sunday11471
21Sunday1147.121
22Tuesday11471
23Tuesday1147.141
24Thursday15492
25Thursday15492
26Saturday18592
27Saturday18592
28Friday25142
29Friday25142
30Saturday2514Y
Sheet1
Cell Formulas
RangeFormula
I1:I29I1=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,$F1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:F30Expression=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1textNO
etaf. It's Perfect. I forgot to color May. My mistake. Thanks once again etaf.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,323
Messages
5,635,596
Members
416,868
Latest member
andiwd

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