2 Conditions to be met per row for Conditional Formatting

[BrainStain]

I am adding a feature to an online worksheet which double-checks if a job is ready to release,
by confirming if 2 conditions are met.

One feature is if that phase is 100% completed, the other that must be met is if that 100% was met on time.
Sometimes a job looks finished if much of the job is complete, but not all of the 12 phases are 100%, or at least the person didnt mark it as 100%,
which leaves in question is it really complete.

I have the conditional formatting working for just one job (12 rows), but it took a long time because I couldnt figure out how to do it as a range.
2 conditions have to be met per line causing my limits of how to do this quickly.
There are 10 different users, each user has their own sheet, (so 10 sheets). Each sheet can have approx. 10 jobs, each job
has 12 phases, so doing the math that could be 10 jobs x 12 phases per user = 120 rows with a conditional format, now theres 10 users, now its 1200 different conditional formatting, since each row has to look at 2 different conditions.

Ive tried it using a range, but cant get it to work since 2 things have to be met per row, is TODAYs date past the Est. Ending Date, and has that phase of 100% been met? (see below in a screen capture)

Column M uses a Range to turn any row Green once it reaches 100%, but that condition only has 1 condition per row, this one listed uses 2 per row.

(NOTE: Dates used here are dummy dates just for testing, but are accurate per when each phase starts, and some phases overlap, like timelines, and were predefined by a start and ship date)
This uses the TODAY function in cell T2, as it increases daily the condition will pass the dates in column S, if that phase hasnt reached 100%, col. M, the cell in Col. M turns red, just to be an alert for that user to finish that phase before that job is finished. Col. M is updated by the user as their guess at how far along they are, so its not automatically updated. They can arrive at 100% early and that cell turns Green. This also can be a check if they put 100% and its not finished just to remove the red cell, but then theyre responsible if that phase really didnt get finished.

Thats as simple as I can state it without taking up too much room here, but if you have questions please ask.

[HansV]

Let's say you want the rule to apply to M5:M16, M20:M31, M35:M46 etc.
Select the entire range. M5 should be the active cell in the selection - you can use the Tab key to ensure this.
Create a rule of type formula with formula

=AND($M5<100, $T$2>=$S5)

Excel will then automatically change 5 to 6 when it applies the rule to M6, to 7 when it applies the rule to M7, etc.
So you only need to create one rule for all those cells in column M.

[BrainStain]

Thanks Hans, I will try that immediately, I knew there had to be some way to use a range, but all the efforts I tried, didn't work.

Don't know if you remember but you're the one who solved another problem I had in this same file, when a job in the middle of many
on a sheet had to be Archived or Deleted, and I had to have a button for users (who aren't familiar with Excel) to click for that, but the button had to know 'Where' down the list of 100 jobs it had to go to remove those 17 lines, without disturbing the whole organized list of jobs, and
now I'm using that same code for several applications in this same file of now about 15 sheets.

You were a life saver, thanks.

[BrainStain]

Well, of course it worked, Hans worked the Magic!!!

I kept a list open of all that I had tried, and that was one of them, maybe I had just not physically selected the range?
The other attempts were =AND(M5:M16<100,T$2>=S5:S16) where I was defining the range in the formula.

Thanks Hans for clarifying this for me. I wonder if one of my problems was that I hadn't made sure that M5 was the active cell after
the range had been selected? (I know some of my attempts the range was defined at the top, but I had not actually selected those
cells, and for sure hadn't made M5 the actively selected cell using the TAB key, that must be the weakness I have in understanding ranges?

Thanks for the Major solution again Hans.

[HansV]

Yes, it's important to make the first (top/left) cell in the selection the active cell, and to refer to that cell in the conditional formatting formula.