Conditional Formatting of cells when a random range is selected elsewhere

[Baiona]

Hey there

Over my head however I think an easy one for those that know...attached is a planner we have put together and I am hoping you can assist. In a nutshell we put a job that's signed off into the spreadsheet "NSW Work Plan"

I have managed to format the spreadsheet so as soon as a cell is highlighted it turns green - but now would like to add that once a job has been allocated labour (in the cells under dates) the first 4 cells on the row denoting Job|Client|Where|What also change colour

So in summary the way our process works is:
1. get sign off on a job
2. put it in the planner (The top two rows are the availability of our crews, which is driven by data in another tab)
3. assign resource to the job (eg 0003), which involves putting a number in anywhere - in this instance 2 technicians in E7 (1st August),
4. first four cells in that row A7:D7 format to another colour - easy flag that resource has been assigned somewhere

Note some of our work only needs to be planned with resource assigned for a day, some are continual works but once there is cell populated anywhere in the date ranges then the first four cells of that row should conditionally format to an assigned colour

Can you help, please

Pierre

[etaf]

for 2007, 2010 or 2013 excel version
Conditional Formatting

Highlight applicable range >>

=$A$5:$D$204


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=COUNT($E5:$LZ5)>0


Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

[Baiona]

Hey

Brilliant. I wasn't far off but you have solved my riddle - Rating below (excellent)

Thank you.

As an aside I put this to Commercial Services but no one responded. Happy to forward the payment to you if you are on there.

Pierre

[etaf]

no, i'm not part of the commercial services team , i not sure of the criteria to be added, when i looked a while ago

- but thanks for the offer

[6StringJazzer]

no, i'm not part of the commercial services team , i not sure of the criteria to be added, when i looked a while ago

You must be a Guru or above.

[etaf]

Thanks jeff, I remember seeing that now some time ago - thanks for the clarification