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.

<tbody>
[TD="class: xl80"][/TD]
[TD="class: xl80, width: 77"]A[/TD]
[TD="class: xl81, width: 99"]B[/TD]
[TD="class: xl81, width: 67"]C[/TD]
[TD="class: xl81, width: 62"]D[/TD]
[TD="class: xl81, width: 84"]E[/TD]
[TD="class: xl81, width: 71"]F[/TD]
[TD="class: xl80, width: 86"]G[/TD]
[TD="class: xl80, width: 57"]H[/TD]
[TD="class: xl82, width: 49"]I[/TD]
[TD="class: xl80, width: 86"]J[/TD]
[TD="class: xl80, width: 90"]K[/TD]
[TD="class: xl81, width: 53"]L[/TD]
[TD="class: xl80, width: 452"]M[/TD]

[TD="class: xl80, width: 77"]1[/TD]
[TD="class: xl80, width: 77"]Customer #[/TD]
[TD="class: xl81, width: 99"]Customer PO #[/TD]
[TD="class: xl81, width: 67"]Customer
Name[/TD]
[TD="class: xl81, width: 62"]Invoice #[/TD]
[TD="class: xl81, width: 84"]Invoice Date[/TD]
[TD="class: xl81, width: 71"]Due Date[/TD]
[TD="class: xl80, width: 86"]Amount
Invoiced[/TD]
[TD="class: xl80, width: 57"]Amount
Paid[/TD]
[TD="class: xl82, width: 49"] Days
Past
Due[/TD]
[TD="class: xl80, width: 86"]Amount
Due[/TD]
[TD="class: xl80, width: 90"]Ship to:
Country[/TD]
[TD="class: xl81, width: 53"]Sales
Order #[/TD]
[TD="class: xl80, width: 452"]Payment Status / Notes[/TD]

[TD="class: xl74"]2[/TD]
[TD="class: xl74"]002045[/TD]
[TD="class: xl75"]123[/TD]
[TD="class: xl75"]ABC0[/TD]
[TD="class: xl76"]002[/TD]
[TD="class: xl77"]18-May-16[/TD]
[TD="class: xl77"]18-May-16[/TD]
[TD="class: xl78"] $ 500.00[/TD]
[TD="class: xl78"] $ -[/TD]
[TD="class: xl79"] 1,252[/TD]
[TD="class: xl78"] $ 500.00[/TD]
[TD="class: xl74"]United States[/TD]
[TD="class: xl76"]5000[/TD]
[TD="class: xl74"]PRE-PAYMENT CREDIT - Ref Check No XXXXX Deposit Date 6/25/2019[/TD]

[TD="class: xl68"]3[/TD]
[TD="class: xl68"]002887[/TD]
[TD="class: xl69"]234[/TD]
[TD="class: xl69"]ABC1[/TD]
[TD="class: xl70"]005[/TD]
[TD="class: xl71"]16-Sep-19[/TD]
[TD="class: xl71"]16-Sep-19[/TD]
[TD="class: xl72"] $ 1,250.00[/TD]
[TD="class: xl72"] $ -[/TD]
[TD="class: xl73"] 36[/TD]
[TD="class: xl72"] $ 1,250.00[/TD]
[TD="class: xl68"]Canada[/TD]
[TD="class: xl70"]7800[/TD]
[TD="class: xl68"]PRE-PAYMENT CREDIT - Ref Check No XXXXX Deposit Date 5/18/2019[/TD]

[TD="class: xl68"]4[/TD]
[TD="class: xl68"]001534[/TD]
[TD="class: xl69"]345[/TD]
[TD="class: xl69"]ZZZ[/TD]
[TD="class: xl70"]PM-010[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl72"] $ (7,000.00)[/TD]
[TD="class: xl72"] $ -[/TD]
[TD="class: xl73"] 1,454[/TD]
[TD="class: xl72"] $ 3,300.00[/TD]
[TD="class: xl68"]United States[/TD]
[TD="class: xl70"]6800[/TD]
[TD="class: xl68"]PRE-PAYMENT CREDIT - Ref Check No XXXXX NR Deposit Date 8/22/2019[/TD]

[TD="class: xl68"]5[/TD]
[TD="class: xl68"]001534[/TD]
[TD="class: xl69"]345[/TD]
[TD="class: xl69"]ZZZ[/TD]
[TD="class: xl70"]010[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl72"] $ 1,500.00[/TD]
[TD="class: xl72"] $ -[/TD]
[TD="class: xl73"] 1,454[/TD]
[TD="class: xl72"] $ 1,500.00[/TD]
[TD="class: xl68"]United States[/TD]
[TD="class: xl70"]6801[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl68"]6[/TD]
[TD="class: xl68"]001534[/TD]
[TD="class: xl69"]345[/TD]
[TD="class: xl69"]ZZZ[/TD]
[TD="class: xl70"]010[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl72"] $ 5,500.00 [/TD]
[TD="class: xl72"] $ -[/TD]
[TD="class: xl73"] 1,454[/TD]
[TD="class: xl72"] $ 5,500.00[/TD]
[TD="class: xl68"]United States[/TD]
[TD="class: xl70"]6802[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl68"]7[/TD]
[TD="class: xl68"]001534[/TD]
[TD="class: xl69"]345[/TD]
[TD="class: xl69"]ZZZ[/TD]
[TD="class: xl70"]010[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl71"]29-Oct-15[/TD]
[TD="class: xl72"] $ 8,900.00[/TD]
[TD="class: xl72"] $ -[/TD]
[TD="class: xl73"] 1,454[/TD]
[TD="class: xl72"] $ 8,900.00[/TD]
[TD="class: xl68"]United States[/TD]
[TD="class: xl70"]6803[/TD]
[TD="class: xl68"][/TD]

</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