Separate color rows after conditional formatting

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello experts

In this sheet I have applied conditional formatting to the amount which are a match with the number and as per columns. I am trying to separate the colored rows but when I sort the data by amount & add level by color, the conditional formatting is changed. How can I separate the colored rows and the white rows.?
Book1
ABCDEFG
1LineAs PerNIMBERDATEAMOUNT
2189TALLY3929-06-20208044.00
3190PORTALRC-3513-06-20208260.00
4191PORTAL7712-06-20208308.00
5192PORTAL11022-06-20208354.00
6193TALLY11022-06-20208354.00
7194PORTAL3926-06-20208386.00
8195PORTAL1102-06-20208489.00
9196PORTAL11624-06-20208547.00
10197TALLY11624-06-20208547.00
11198PORTAL1910-06-20208784.00
12199TALLY1910-06-20208784.00
13200PORTAL0376/20-2129-06-20208805.00
14201TALLY0376/20-2129-06-20208805.00
15202PORTAL8113-06-20208830.00
16203TALLY8113-06-20208830.00
17204PORTAL6710-06-20208868.00
18205TALLY6710-06-20208868.00
19206PORTAL0017/20-2103-06-20208939.00
20207TALLY0017/20-2103-06-20208939.00
21208PORTAL6030-06-20208982.00
22209PORTAL3418-06-20209139.00
23210TALLY3418-06-20209139.20
24211PORTALKA-0352-2020-2108-06-20209338.00
25212TALLYKA-0352-2020-2108-06-20209338.00
26213PORTAL29324-06-20209450.00
27214TALLY29324-06-20209450.00
28215PORTAL0076/20-2129-06-20209457.00
29216PORTAL0076/20-2129-06-20209457.00
30217TALLY0076/20-2129-06-20209457.00
31218PORTAL1/25726-06-20209639.00
32219PORTALRC-1811-06-20209912.00
33220PORTALKA-0029-2020-2108-05-20209975.00
34221PORTALKA-0050-2020-2111-05-20209975.00
35222TALLYKA-0029-2020-2108-05-20209975.00
36223TALLYKA-0050-2020-2111-05-20209975.00
37224PORTAL0908-06-202010014.00
38225TALLY0908-06-202010014.00
39247PORTAL7312-06-202011582.00
40248PORTAL5203-06-202011626.00
41249TALLY5203-06-202011626.00
42250PORTAL1014-05-202011853.00
43251PORTALSRT-R-9012-06-202012025.00
44252TALLYSRT-R-9012-06-202012025.00
45253PORTAL12426-06-202012037.00
46254TALLY12426-06-202012037.00
47255PORTAL3027-05-202012219.00
48256TALLY3027-05-202012219.00
49257PORTALSM/2020-21/2310101-04-202012732.00
50258PORTAL6610-06-202012739.00
51259TALLY6610-06-202012739.00
52398TALLYNANA60534.00
53399PORTALKA-2021-15972430-06-202060673.98
54402TALLY41201-05-202067517.00
55403PORTALM20063000123930630-06-202072735.20
56404PORTAL14929-06-2020114534.00
57405TALLY14929-06-2020114534.00
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:G57Expression=OR(AND(E2=E3,F2=F3),AND(E2=E1,F2=F1))textNO
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
RAJESH1960 said:
Hello experts

In this sheet I have applied conditional formatting to the amount which are a match with the number and as per columns. I am trying to separate the colored rows but when I sort the data by amount & add level by color, the conditional formatting is changed. How can I separate the colored rows and the white rows.?
Book1
ABCDEFG
1LineAs PerNIMBERDATEAMOUNT
2189TALLY3929-06-20208044.00
3190PORTALRC-3513-06-20208260.00
4191PORTAL7712-06-20208308.00
5192PORTAL11022-06-20208354.00
6193TALLY11022-06-20208354.00
7194PORTAL3926-06-20208386.00
8195PORTAL1102-06-20208489.00
9196PORTAL11624-06-20208547.00
10197TALLY11624-06-20208547.00
11198PORTAL1910-06-20208784.00
12199TALLY1910-06-20208784.00
13200PORTAL0376/20-2129-06-20208805.00
14201TALLY0376/20-2129-06-20208805.00
15202PORTAL8113-06-20208830.00
16203TALLY8113-06-20208830.00
17204PORTAL6710-06-20208868.00
18205TALLY6710-06-20208868.00
19206PORTAL0017/20-2103-06-20208939.00
20207TALLY0017/20-2103-06-20208939.00
21208PORTAL6030-06-20208982.00
22209PORTAL3418-06-20209139.00
23210TALLY3418-06-20209139.20
24211PORTALKA-0352-2020-2108-06-20209338.00
25212TALLYKA-0352-2020-2108-06-20209338.00
26213PORTAL29324-06-20209450.00
27214TALLY29324-06-20209450.00
28215PORTAL0076/20-2129-06-20209457.00
29216PORTAL0076/20-2129-06-20209457.00
30217TALLY0076/20-2129-06-20209457.00
31218PORTAL1/25726-06-20209639.00
32219PORTALRC-1811-06-20209912.00
33220PORTALKA-0029-2020-2108-05-20209975.00
34221PORTALKA-0050-2020-2111-05-20209975.00
35222TALLYKA-0029-2020-2108-05-20209975.00
36223TALLYKA-0050-2020-2111-05-20209975.00
37224PORTAL0908-06-202010014.00
38225TALLY0908-06-202010014.00
39247PORTAL7312-06-202011582.00
40248PORTAL5203-06-202011626.00
41249TALLY5203-06-202011626.00
42250PORTAL1014-05-202011853.00
43251PORTALSRT-R-9012-06-202012025.00
44252TALLYSRT-R-9012-06-202012025.00
45253PORTAL12426-06-202012037.00
46254TALLY12426-06-202012037.00
47255PORTAL3027-05-202012219.00
48256TALLY3027-05-202012219.00
49257PORTALSM/2020-21/2310101-04-202012732.00
50258PORTAL6610-06-202012739.00
51259TALLY6610-06-202012739.00
52398TALLYNANA60534.00
53399PORTALKA-2021-15972430-06-202060673.98
54402TALLY41201-05-202067517.00
55403PORTALM20063000123930630-06-202072735.20
56404PORTAL14929-06-2020114534.00
57405TALLY14929-06-2020114534.00
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:G57Expression=OR(AND(E2=E3,F2=F3),AND(E2=E1,F2=F1))textNO
Click to expand...
Solved.....
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
241
Eric W
Eric W
H
  • Solved
Special Sort, Sort Groups.
Replies
9
Views
390
Akuini
Akuini
RAJESH1960
  • Solved
Conditional formatting with 3 conditions
Replies
7
Views
362
GraH
GraH
V
  • Solved
Multiple Conditional Formatting
Replies
3
Views
310
PeteWright
PeteWright
H
  • Solved
Reference to ...ListColumns("<name>"").index in Conditional Formatting Formula
Replies
3
Views
487
Hans Troost
H

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,071
Latest member
cdnMech

Share this page

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
Back
Top