Excel Conditional Format / IF Statement

[mlbarry]

In the below example I have a list of open invoices sorted in alphabetic order, Column C, and customer number, Column A. I want to have the color of Column: C - Rows: 4,5,6 and 7 changed to RED because M4 contains the letters NR. My spread sheet has 6,000 to 20,000 rows and column A and C are consistent through out the spread sheet. I need the color change to change all of the Customers Names in column C that have the same customer number. So if MXX contains NR and if MXX equals or the same A4 then change C4,5,6 and 7 RED.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer #	Customer PO #	Customer Name	Invoice #	Invoice Date	Due Date	Amount Invoiced	Amount Paid	Days Past Due	Amount Due	Ship to: Country	Sales Order #	Payment Status / Notes
2	002045	123	ABC0	002	18-May-16	18-May-16	$ 500.00	$ -	1,252	$ 500.00	United States	5000	PRE-PAYMENT CREDIT - Ref Check No XXXXX Deposit Date 6/25/2019
3	002887	234	ABC1	005	16-Sep-19	16-Sep-19	$ 1,250.00	$ -	36	$ 1,250.00	Canada	7800	PRE-PAYMENT CREDIT - Ref Check No XXXXX Deposit Date 5/18/2019
4	001534	345	ZZZ	PM-010	29-Oct-15	29-Oct-15	$ (7,000.00)	$ -	1,454	$ 3,300.00	United States	6800	PRE-PAYMENT CREDIT - Ref Check No XXXXX NR Deposit Date 8/22/2019
5	001534	345	ZZZ	010	29-Oct-15	29-Oct-15	$ 1,500.00	$ -	1,454	$ 1,500.00	United States	6801
6	001534	345	ZZZ	010	29-Oct-15	29-Oct-15	$ 5,500.00	$ -	1,454	$ 5,500.00	United States	6802
7	001534	345	ZZZ	010	29-Oct-15	29-Oct-15	$ 8,900.00	$ -	1,454	$ 8,900.00	United States	6803

<tbody>
</tbody>

 

[Fluff]

Select C2:C last row > Conditional formatting > New rule > use a formula > & use
=COUNTIFS($A:$A,A2,$C:$C,C2,$M:$M,"* NR *")>0

 

[mlbarry]

WOW the first time, Thank you very much.

Thread Closed.

 

[Fluff]

You're welcome & thanks for the feedback