Conditional formatting with 3 conditions

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello guys,
I need some help in conditional formatting formula with multiple conditions. I am trying to find out which Invoice number are not matching in column E. If the date is same in cell F2 and F3, and in the As per column one is Portal and one is Tally then the invoice number should be the same in column E. If they are not the same with the above 2 conditions I want to color the cells E2 and E3 and so on...
IF B2<>3, F2=F3, THEN CHECK IF E2=E3,IF NOT TRUE COLOR E2&E3
In short, I want to color the column E cells if date is same, if as per column one is portal and one is tally & if the numbers in column E are different.
QUERY TO MATCH INVOICE NO..xlsx
ABCDEF
1LineAs PerInvoice numberInvoice Date
21PORTAL2021073128-08-2020
32TALLY2021073128-08-2020
43PORTAL093A/2020-2108-09-2020
54PORTALS3/0019/2020-2116-06-2020
65TALLY1916-06-2020
76PORTALS3/0115/2020-2119-11-2020
87TALLYS3/0115/2020-2119-11-2020
98PORTAL1562/20-2101-02-2021
109TALLY156201-03-2021
1110TALLY439619-01-2021
1211PORTAL390/20-2120-07-2020
1312TALLY39020-07-2020
1413PORTAL1262/20-2122-12-2020
1514TALLY1262/20-2122-12-2020
1615PORTAL1955/20-2129-03-2021
1716TALLY195529-03-2021
1817TALLY121010-12-2020
1918PORTAL1210/20-2110-12-2020
2019PORTAL1561/20-2101-02-2021
2120TALLY1561/20-2101-03-2021
2221TALLY109901-07-2020
2322PORTAL781/20-2124-09-2020
2423TALLY781/20-2124-09-2020
2524TALLY410712-10-2020
2625PORTAL1481/20-2120-01-2021
2726TALLY1481/20-2120-01-2021
2827TALLY10903-06-2020
2928PORTAL109/20-2103-06-2020
3029PORTAL1042/20-2113-11-2020
3130TALLY104213-11-2020
3231PORTAL934/20-2121-10-2020
3332TALLY934/20-2121-10-2020
3433PORTAL1244/20-2117-12-2020
3534TALLY1244/20-2117-12-2020
3635PORTAL143/20-2106-06-2020
3736TALLY143/20-2106-06-2020
3837PORTAL780/20-2124-09-2020
3938TALLY78024-09-2020
4039PORTAL940/20-2122-10-2020
4140TALLY940/20-2122-10-2020
4241PORTAL389/20-2120-07-2020
4342TALLY38920-07-2020
4443TALLY138905-01-2021
4544PORTAL170/20-2111-06-2020
4645TALLY17011-06-2020
4746PORTAL630/20-2101-09-2020
4847TALLY63001-09-2020
4948PORTAL746/20-2117-09-2020
5049TALLY74617-09-2020
B2B + Tally Original (2)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi, maybe like this?
Book1
ABCDEF
1LineAs PerInvoice numberInvoice Date
21PORTAL2021073128/08/2020
32TALLY2021073128/08/2020
43PORTAL093A/2020-218/09/2020
54PORTALS3/0019/2020-2116/06/2020
65TALLY1916/06/2020
76PORTALS3/0115/2020-2119/11/2020
87TALLYS3/0115/2020-2119/11/2020
98PORTAL1562/20-211/02/2021
109TALLY15621/03/2021
1110TALLY439619/01/2021
1211PORTAL390/20-2120/07/2020
1312TALLY39020/07/2020
1413PORTAL1262/20-2122/12/2020
1514TALLY1262/20-2122/12/2020
1615PORTAL1955/20-2129/03/2021
1716TALLY195529/03/2021
1817TALLY121010/12/2020
1918PORTAL1210/20-2110/12/2020
2019PORTAL1561/20-211/02/2021
2120TALLY1561/20-211/03/2021
2221TALLY10991/07/2020
2322PORTAL781/20-2124/09/2020
2423TALLY781/20-2124/09/2020
2524TALLY410712/10/2020
2625PORTAL1481/20-2120/01/2021
2726TALLY1481/20-2120/01/2021
2827TALLY1093/06/2020
2928PORTAL109/20-213/06/2020
3029PORTAL1042/20-2113/11/2020
3130TALLY104213/11/2020
3231PORTAL934/20-2121/10/2020
3332TALLY934/20-2121/10/2020
3433PORTAL1244/20-2117/12/2020
3534TALLY1244/20-2117/12/2020
3635PORTAL143/20-216/06/2020
3736TALLY143/20-216/06/2020
3837PORTAL780/20-2124/09/2020
3938TALLY78024/09/2020
4039PORTAL940/20-2122/10/2020
4140TALLY940/20-2122/10/2020
4241PORTAL389/20-2120/07/2020
4342TALLY38920/07/2020
4443TALLY13895/01/2021
4544PORTAL170/20-2111/06/2020
4645TALLY17011/06/2020
4746PORTAL630/20-211/09/2020
4847TALLY6301/09/2020
4948PORTAL746/20-2117/09/2020
5049TALLY74617/09/2020
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E50Expression=OR(AND(ISEVEN(ROW());$F2=$F3;$B2<>$B3;$E2<>$E3);AND(ISODD(ROW());$F2=$F1;$B2<>$B1;$E2<>$E1))textNO


or
Excel Formula:
(ISEVEN(ROW())*($F2=$F3)*(B2<>$B3)*($E2<>$E3))+(ISODD(ROW())*($F2=$F1)*($B2<>$B1)*($E2<>$E1))=1
 
Upvote 0
Hi, maybe like this?
Book1
ABCDEF
1LineAs PerInvoice numberInvoice Date
21PORTAL2021073128/08/2020
32TALLY2021073128/08/2020
43PORTAL093A/2020-218/09/2020
54PORTALS3/0019/2020-2116/06/2020
65TALLY1916/06/2020
76PORTALS3/0115/2020-2119/11/2020
87TALLYS3/0115/2020-2119/11/2020
98PORTAL1562/20-211/02/2021
109TALLY15621/03/2021
1110TALLY439619/01/2021
1211PORTAL390/20-2120/07/2020
1312TALLY39020/07/2020
1413PORTAL1262/20-2122/12/2020
1514TALLY1262/20-2122/12/2020
1615PORTAL1955/20-2129/03/2021
1716TALLY195529/03/2021
1817TALLY121010/12/2020
1918PORTAL1210/20-2110/12/2020
2019PORTAL1561/20-211/02/2021
2120TALLY1561/20-211/03/2021
2221TALLY10991/07/2020
2322PORTAL781/20-2124/09/2020
2423TALLY781/20-2124/09/2020
2524TALLY410712/10/2020
2625PORTAL1481/20-2120/01/2021
2726TALLY1481/20-2120/01/2021
2827TALLY1093/06/2020
2928PORTAL109/20-213/06/2020
3029PORTAL1042/20-2113/11/2020
3130TALLY104213/11/2020
3231PORTAL934/20-2121/10/2020
3332TALLY934/20-2121/10/2020
3433PORTAL1244/20-2117/12/2020
3534TALLY1244/20-2117/12/2020
3635PORTAL143/20-216/06/2020
3736TALLY143/20-216/06/2020
3837PORTAL780/20-2124/09/2020
3938TALLY78024/09/2020
4039PORTAL940/20-2122/10/2020
4140TALLY940/20-2122/10/2020
4241PORTAL389/20-2120/07/2020
4342TALLY38920/07/2020
4443TALLY13895/01/2021
4544PORTAL170/20-2111/06/2020
4645TALLY17011/06/2020
4746PORTAL630/20-211/09/2020
4847TALLY6301/09/2020
4948PORTAL746/20-2117/09/2020
5049TALLY74617/09/2020
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E50Expression=OR(AND(ISEVEN(ROW());$F2=$F3;$B2<>$B3;$E2<>$E3);AND(ISODD(ROW());$F2=$F1;$B2<>$B1;$E2<>$E1))textNO
GraH. I applied the formula in a new rule, but I am getting an error. There is a problem with the formula. The conditional formatting is not accepting the formula.
 
Upvote 0
I'm on EU settings using ";" between arguments. Try replacing them with a comma. Or use the formula I added just a bit later.
 
Upvote 0
I'm on EU settings using ";" between arguments. Try replacing them with a comma. Or use the formula I added just a bit later.
I got the formula right but it is not coloring so many cells. Like, rows 5-6, 45-46, 47-48, 49-50. This is just a sample data. I have more than 12000 rows to match in the original data.
 
Upvote 0
Might this be better RAJESH1960?
Book1
ABCDEF
1LineAs PerInvoice numberInvoice Date
21PORTAL2021073128/08/2020
32TALLY2021073128/08/2020
43PORTAL093A/2020-218/09/2020
54PORTALS3/0019/2020-2116/06/2020
65TALLY1916/06/2020
76PORTALS3/0115/2020-2119/11/2020
87TALLYS3/0115/2020-2119/11/2020
98PORTAL1562/20-211/02/2021
109TALLY15621/03/2021
1110TALLY439619/01/2021
1211PORTAL390/20-2120/07/2020
1312TALLY39020/07/2020
1413PORTAL1262/20-2122/12/2020
1514TALLY1262/20-2122/12/2020
1615PORTAL1955/20-2129/03/2021
1716TALLY195529/03/2021
1817TALLY121010/12/2020
1918PORTAL1210/20-2110/12/2020
2019PORTAL1561/20-211/02/2021
2120TALLY1561/20-211/03/2021
2221TALLY10991/07/2020
2322PORTAL781/20-2124/09/2020
2423TALLY781/20-2124/09/2020
2524TALLY410712/10/2020
2625PORTAL1481/20-2120/01/2021
2726TALLY1481/20-2120/01/2021
2827TALLY1093/06/2020
2928PORTAL109/20-213/06/2020
3029PORTAL1042/20-2113/11/2020
3130TALLY104213/11/2020
3231PORTAL934/20-2121/10/2020
3332TALLY934/20-2121/10/2020
3433PORTAL1244/20-2117/12/2020
3534TALLY1244/20-2117/12/2020
3635PORTAL143/20-216/06/2020
3736TALLY143/20-216/06/2020
3837PORTAL780/20-2124/09/2020
3938TALLY78024/09/2020
4039PORTAL940/20-2122/10/2020
4140TALLY940/20-2122/10/2020
4241PORTAL389/20-2120/07/2020
4342TALLY38920/07/2020
4443TALLY13895/01/2021
4544PORTAL170/20-2111/06/2020
4645TALLY17011/06/2020
4746PORTAL630/20-211/09/2020
4847TALLY6301/09/2020
4948PORTAL746/20-2117/09/2020
5049TALLY74617/09/2020
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E50Expression=(($F2=$F3)*(B2<>$B3)*($E2<>$E3))+(($F2=$F1)*($B2<>$B1)*($E2<>$E1))=1textNO
 
Upvote 0
Solution
Might this be better RAJESH1960?
Book1
ABCDEF
1LineAs PerInvoice numberInvoice Date
21PORTAL2021073128/08/2020
32TALLY2021073128/08/2020
43PORTAL093A/2020-218/09/2020
54PORTALS3/0019/2020-2116/06/2020
65TALLY1916/06/2020
76PORTALS3/0115/2020-2119/11/2020
87TALLYS3/0115/2020-2119/11/2020
98PORTAL1562/20-211/02/2021
109TALLY15621/03/2021
1110TALLY439619/01/2021
1211PORTAL390/20-2120/07/2020
1312TALLY39020/07/2020
1413PORTAL1262/20-2122/12/2020
1514TALLY1262/20-2122/12/2020
1615PORTAL1955/20-2129/03/2021
1716TALLY195529/03/2021
1817TALLY121010/12/2020
1918PORTAL1210/20-2110/12/2020
2019PORTAL1561/20-211/02/2021
2120TALLY1561/20-211/03/2021
2221TALLY10991/07/2020
2322PORTAL781/20-2124/09/2020
2423TALLY781/20-2124/09/2020
2524TALLY410712/10/2020
2625PORTAL1481/20-2120/01/2021
2726TALLY1481/20-2120/01/2021
2827TALLY1093/06/2020
2928PORTAL109/20-213/06/2020
3029PORTAL1042/20-2113/11/2020
3130TALLY104213/11/2020
3231PORTAL934/20-2121/10/2020
3332TALLY934/20-2121/10/2020
3433PORTAL1244/20-2117/12/2020
3534TALLY1244/20-2117/12/2020
3635PORTAL143/20-216/06/2020
3736TALLY143/20-216/06/2020
3837PORTAL780/20-2124/09/2020
3938TALLY78024/09/2020
4039PORTAL940/20-2122/10/2020
4140TALLY940/20-2122/10/2020
4241PORTAL389/20-2120/07/2020
4342TALLY38920/07/2020
4443TALLY13895/01/2021
4544PORTAL170/20-2111/06/2020
4645TALLY17011/06/2020
4746PORTAL630/20-211/09/2020
4847TALLY6301/09/2020
4948PORTAL746/20-2117/09/2020
5049TALLY74617/09/2020
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E50Expression=(($F2=$F3)*(B2<>$B3)*($E2<>$E3))+(($F2=$F1)*($B2<>$B1)*($E2<>$E1))=1textNO
It's Perfect. Thanks GraH.
 
Upvote 0
Great! Thanks for the feedback and mark. Glad I could help after finally seeing the pattern in only 2 iterations.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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