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

davin

New Member
Joined
Mar 19, 2002
Messages
41
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
Hi Jay:
Can you elaborate on the conditional format formula you gave in case 2) in response to a post from Davin ... THANKS!
 
Upvote 0
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
 
Upvote 0
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 !
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top