Highlight all jobs belonging to one client

mattr93

New Member
Joined
May 11, 2018
Messages
2
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try this.

AB
1CompanyOrder date
2Alpha02/01/2006
3Beta07/01/2010
4Gamma02/01/2009
5Alpha02/01/2007
6Beta02/01/2007

<tbody>
</tbody>

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())))))
 
Last edited:
Upvote 0
My spreadsheet currently looks something like:

ABCDEFGH
1
OrganisationSubjectStatusStateInvoicedEnd DateExpiry DateIn Date
2Client AJob ACompleteQuotationNo17/09/201517/09/2016TRUE
3Client AJob BCompleteOrderYes24/09/201524/09/2025FALSE
4Client AJob CCompleteOrderYes12/10/201512/10/2025FALSE
5Client AJob DCompleteQuotationNo25/10/201525/10/2016TRUE
6Client AJob ECompleteQuotationNo04/11/201504/11/2016TRUE
7Client AJob FCompleteOrderYes16/11/201516/11/2025FALSE

<tbody>
</tbody>

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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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