# Thread: Highlight all jobs belonging to one client Thanks: 0 Likes: 0

1. ## Highlight all jobs belonging to one client

I have a spreadsheet with a list of jobs and the client.
Any job that has an order has an expiry date of 10 years, anything else has an expiry date of 1 year.
Any job that is still in date has been highlighted Green using conditional formatting.

I would like to highlight all rows with the same client name if that client has at least 1 entry which is an order (so that the order overrides the expiry date). I have been racking my brains all day over this so any help would be much appreciated. I'm only a basic user of excel so I do apologise if i have missed something rather simple.

2. ## Re: Highlight all jobs belonging to one client

Oops! What I posted doesn't work.

3. ## Re: Highlight all jobs belonging to one client

Try this.

 A B 1 Company Order date 2 Alpha 02/01/2006 3 Beta 07/01/2010 4 Gamma 02/01/2009 5 Alpha 02/01/2007 6 Beta 02/01/2007

Conditional formatting formula applied to A2:B6.

=COUNT(1/((\$A2=\$A\$2:\$A\$6)*(\$B\$2:\$B\$6>=DATE(YEAR(TODAY())-10,MONTH(TODAY()),DAY(TODAY())))))

4. ## Re: Highlight all jobs belonging to one client

My spreadsheet currently looks something like:

 A B C D E F G H 1 Organisation Subject Status State Invoiced End Date Expiry Date In Date 2 Client A Job A Complete Quotation No 17/09/2015 17/09/2016 TRUE 3 Client A Job B Complete Order Yes 24/09/2015 24/09/2025 FALSE 4 Client A Job C Complete Order Yes 12/10/2015 12/10/2025 FALSE 5 Client A Job D Complete Quotation No 25/10/2015 25/10/2016 TRUE 6 Client A Job E Complete Quotation No 04/11/2015 04/11/2016 TRUE 7 Client A Job F Complete Order Yes 16/11/2015 16/11/2025 FALSE

I have used conditional formatting to highlight the row dependant on the value of Column H.
If H = True then the row is Green. If H = FALSE then the row is RED.

I need the TRUE values to override the FALSE values if the value in Column A is the same.

Hopefully that explains what i need a bit better

5. ## Re: Highlight all jobs belonging to one client

Rules apply to \$A\$2:\$H\$7

Order of the rules, top to bottom, as they appear in the 'Conditional Formatting Manager':

Green row if the Organization has TRUE in any record:: =MATCH(\$A2&TRUE(),\$A\$2:\$A\$7&\$H\$2:\$H\$7,0)
Red row when column H is false:: =\$H2=FALSE
Green row when column H is TRUE formula:: =\$H2