Conditional formatting with formula referencing range in different cell

**pethaa1791** · 10-21-2008, 02:51 PM

Hi,

It's probably pretty simple, but I'm already breaking my head over it for a couple of hours now.

I want to set a conditional format to cell A1 with a value in cell P1
When the value in P1 is between 0 and 10 the conditional value in P1 should be set.

Note
cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.

Thanks!

**NBVC** · 10-21-2008, 02:56 PM

Select the Formula Is option instead of Cell Value Is..

Then enter a condition formula that would result in TRUE/FALSE.

e.g. in Cell A1, set condition: =AND(P1>=0,P1<=10)

**pethaa1791** · 10-21-2008, 03:06 PM

I've done that, but I think the first condition which is putting a border around the cell when it has a value in it interferes with the second formula condition which is referencing column P

See example

**NBVC** · 10-21-2008, 03:22 PM

If you switch the conditions around, then the "red" will override the "bordering"

What exactly do you want to have happen in column A.

Or try this for your 1st condition: =AND(A2<>"",P2>=0,P2<=10)

And format with Borders and Red background

The 2nd condition would be Cell Value Is >> Not Equal To >> ""

And format with just Borders.

**pethaa1791** · 10-21-2008, 03:34 PM

The cell in column A should have a border when it has a content and should be colored red if the value in column P is between 1 and 10

In effect:
The cell in column A can have a border without a red colored cell.
Or the cell in column A can have a border with a red colered cell, all dependent on the value in column P

There never can be a value in Column P when the corresponding cell in Column A has no value.

I basically need a conditional format which formats the cell in column A when one condition or both conditions are true. The format of column A is different if only one condition is true from the format when both conditions are true

I hope I didn't even complicate it more with this explanation.

Really appreciate your effort though

Thanks!!!

**NBVC** · 10-21-2008, 03:36 PM

Did you see my edit to my last post....

Or try this for your 1st condition: =AND(A2<>"",P2>=0,P2<=10)

And format with Borders and Red background

The 2nd condition would be Cell Value Is >> Not Equal To >> ""

And format with just Borders.

Try that.

**pethaa1791** · 10-21-2008, 03:46 PM

Still no luck

It won't color the cell red with the given condition, but it puts borders around the cells in column A which have contents.

It looks like the << not equal "" >> condition overules the formula condition everytime.

This is what keep me puzzlin' now for a couple of hours.