Hi Yogi,
Suppose I have the following:
Test Cell: F16
Value cell housing the criteria: A1 (suppose it is currently 10)
This means that is there is a 25 in cell A1, I want the Conditional Formatting to kick in for cells (rows, etc.) in rows 26 or above. In this case, I do not want the cell to be changed. When 10 is in cell A1, then F16 is highlighted as it evaluates to true in rows 11 and above.
So, I am in F16 and Format>Conditional Format>Formula Is...
=ROW()> in this case is 16 greater than...
ROW(INDIRECT(ADDRESS(MAX(1,A1),COLUMN(),4,1)))
Working inside out...
1. ADDRESS(MAX(1,A1),COLUMN(),4,1)
Standard ADDRESS function call, but the reference to cell A1 cannot be zero, as I am using this result in a call to ROW(...) and it would throw an error if it had zero in the A1 (ROW 0).
The column() isn't really used, but is required for the address function. You can specify a column if you wish.
With 10 in A1, and F16 my target, active cell, this returns F10, which is text.
2. INDIRECT(....)
Evaluates the address as if it were a call to cell F10, so instead of reading text, Excel is looking at cell F10.
3. ROW(INDIRECT(...))
Essentially performs the same as if you wrote =ROW(F10)
Putting it all together, we have
=16>10 = TRUE and conditional formatting kicks in.
This can be improved, as I didn't test if there were text in A1, and I've read that using INDIRECT and ADDRESS together is inefficient.
Regards,
Jay