Excel Conditional Format / IF Statement

mlbarry

New Member
Joined
Sep 26, 2012
Messages
47
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.
ABCDEFGHIJKLM
1Customer #Customer PO #Customer
Name
Invoice #Invoice DateDue DateAmount
Invoiced
Amount
Paid
Days
Past
Due
Amount
Due
Ship to:
Country
Sales
Order #
Payment Status / Notes
2002045123ABC000218-May-1618-May-16 $ 500.00 $ - 1,252 $ 500.00United States5000PRE-PAYMENT CREDIT - Ref Check No XXXXX Deposit Date 6/25/2019
3002887234ABC100516-Sep-1916-Sep-19 $ 1,250.00 $ - 36 $ 1,250.00Canada7800PRE-PAYMENT CREDIT - Ref Check No XXXXX Deposit Date 5/18/2019
4001534345ZZZPM-01029-Oct-1529-Oct-15 $ (7,000.00) $ - 1,454 $ 3,300.00United States6800PRE-PAYMENT CREDIT - Ref Check No XXXXX NR Deposit Date 8/22/2019
5001534345ZZZ01029-Oct-1529-Oct-15 $ 1,500.00 $ - 1,454 $ 1,500.00United States6801
6001534345ZZZ01029-Oct-1529-Oct-15 $ 5,500.00 $ - 1,454 $ 5,500.00United States6802
7001534345ZZZ01029-Oct-1529-Oct-15 $ 8,900.00 $ - 1,454 $ 8,900.00United States6803

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,454
Office Version
365
Platform
Windows
Select C2:C last row > Conditional formatting > New rule > use a formula > & use
=COUNTIFS($A:$A,A2,$C:$C,C2,$M:$M,"* NR *")>0
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,454
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,084,905
Messages
5,380,525
Members
401,687
Latest member
elisa pathros

Some videos you may like

This Week's Hot Topics

Top