Conditional Format.........Help!

[davin]

Is there a formula or a conditional format that will turn an entire range of cells a different color, when the value of one cell is above or below a specific range?

[Jay Petrulis]

Hi Davin,

Use Conditional Formatting > Formula Is:

1. Formatting a cell if the value is above some value or below some other value:
=OR($B$1>$A$1,$B$1<$C$1)

2. Formatting if the row is greater than the row corresponding to the value of another cell (i.e A1 has value of 15, highlight all rows greater than 15)

=ROW()>ROW(INDIRECT(ADDRESS(MAX(1,A1),COLUMN(),4,1)))

Format to suit. Modify the cell references as needed.

Bye,
Jay

[Yogi Anand]

Hi Jay:
Can you elaborate on the conditional format formula you gave in case 2) in response to a post from Davin ... THANKS!

[Jay Petrulis]

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

[Yogi Anand]

Thanks Jay:
I hear you alright ... but I will acytually have to key a couple of things in to properly digest it ... T H A N K S !