Very Custom Conditional Formatting with cell range, Help!

Originally Posted by nflsales

Is always M1 first?

Yes, M1 and M2 always first.... and end it with P1 or P2,

if M1 - P2 it won't work/happened, and vice versa

I've found the solution of this. here's step by step to solve the riddle

1. I add another helper table for this. see picture below. The task is to detect in what column do "M1" and "P1" exist in a certain row, and tell it with column number

ConditionalFormatting2.JPG

2. the function to show the column number is

Formula:

Please Login or Register  to view this content.

minus (-) after row(), is meant to address you the row that you need to detect. A7 : BA7 is to search where M1, P1, M2, P2, is placed

3. after that, just block all the column / area where you want the conditional formatting rules. and use this function in conditional formatting.

Formula:

Please Login or Register  to view this content.

A96 refers to step no 2, and sign >= for M1, and <= for P1, basically it tells M1 =< Green <= P1 (the color will be green)

Done~

Hope it helps.

thanks