Fill a range of cells based on certain criteria

I did it only for the first rule, but next conditions shall be done the same way.

so for Ryan conformed I selected all cells with calendar (starting B4 and ending H575), from conditional formatting choosen new rule, and then use formula. and as formula I wrote (not just point and click, because by default it will use absolute addresses and we need relative ones):

Formula:

Please Login or Register  to view this content.

Note that you will need to use comma not selicolon as separator.

this checks 1 of 4 sets of 2 conditions (as any set of 2 conditions would make the cell green, we combine these sets with OR function):
- current cell content is Ryan and cell 3 rows below i confirmed. This two conditions has to be fulfilled at the same time, thus we connect them by AND (this is a condition which will "lit" topmost cell in 4-cell range)
- cell one above is Ryan AND cell two below is confirmned
- cell two above is Ryan AND cell one below is confirmned
and for last cell in range cell 3 above ryan AND current cell = confirmed

Note that it may seem it uses references to header cells - not really, checking "- cell one above is Ryan AND cell two below is confirmned" will obviously fail in B4, but when applied to B5 could be right (if it's not Jan 1st :-))


I hope that after such description preparing yourself next conditions will be pretty simple.

You can see this formula when you have active cell B4 and use Conditional formatting, Manage rules, Edit rule

Glad to see it was working, and appreciate comment made in reputation acknowlegdement about "my explanation based approach".

8-)


This approach fits my proffesional profile (who knows - knows :-))