Conditional formatting cells based on multiple cell contents

Hi,

I'm trying to apply conditional formatting to a job tracker excel sheet I'm working on. The attached file displays one job and associated details.

I'm using a couple of rules at the minute to change the colour of the top line of the table based on the contents of one cell (C55) This seems to work fine so far.

It's used to highlight for attention in red, if the production week is an invalid value or blank. A valid number (1-52) provides green formatting on the top line.

I've tried adding a third rule to change that top line once more if there is a value in S4:U4. There doesn't need to be any data checking on that, just not a blank cell.

The third rule is where I'm struggling - I'd just like to remove any formatting from the first line and having tried butchering the previous two rules in the creation of a third, I've hit a wall.

Admittedly my Excel skills aren't too hot - I've had help thus far in creating the formulas used in the conditional rules powering this. It might not even be the right or best way of achieving this.

It may be worth noting that there will be many of these tables on one worksheet; likely up to 1500 or so and each with 3 conditional formatting rules applied, if I'm even going about this the right way. I don't know if there's a more efficient way of achieving this.

I'd really appreciate any help or advice on this

Try chaning the formula for the red color to

Formula:

Please Login or Register  to view this content.

since S4:U4 is a merged cells range you only need to point to S4 as excel will store only data in the first cell of a merged range.

Hi Roel, thanks for your reply

I think this only gives me two state changes on that top line - I'm looking for a third

The sheet will be full of blank tables to start with; top line will be red as there is no valid entry in C5. Once it has a valid entry the top line turns green. Up until this point there won't be an entry in S4.
Upon entering any value in S4 I'd like the top line to change the formatting fill colour once more to a different colour.

As a secondairy solutions I also made two new red rules that will also indicate the wrong cell

I removed the green rule, and painted the topline green by default, that is another way to reduce the number of rules
i prefer using CF only for errormessages or specific status codes, when everything is as it supposed to be I dont use a rule to show that as in more complex sheets it might double the rules

I appreciate your continued help

The sheet is intended to be filled out incrementally, so different fields will be populated on different days IRL.
The top line first once an order is received. Some days later the production week will be known and filled out. At this point it should turn the top line green on valid numeric entry
Some days later a dispatch date will be populated and the top line should turn white or 'no fill'.

I like that you tried to make it more efficient by starting out green but if someone sees green initially, the assumption would be that it has a production date and the respective field may not be checked.
These conditional formats are really to save on human error. Do you think we need a third rule to achieve this?

Try adding this to CF and format as clear:
(apply to $B$3:$U$3)

=$S4<>""

see attached.

Brilliant - that's exacly what I need!

Thankyou Gregb11 & Roel Jongman

You're welcome. Thanks for the feedback and rep!