Conditional formatting based on Cell Value and another cell's valu

[Nebulous]

In Excel 2007...

If E5>0 and E3="x" I want E5 to be shaded red.

How can I accomplish this?

Even better would be:

If E5>0 and E3="x" I want E5 to be shaded "the same color as E3" (that is,
to use the shaading of E3 as a variable. Doubt that's possible).

Any help is GREATLY appreciated!

jb

 

[Pete_UK]

Select E5, bring up the conditional formatting dialogue box and in the
first panel select Formula Is rather than Cell Value Is and then enter
this formula:

=AND(E5>0,E3="x")

Then click the Format button, Patterns tab and choose Red. OK your way
out.

If E3 is shaded manually rather than through conditional formatting,
then you can't use CF to get E5 to change to the same colour as E3 -
you'd need some VBA to do that.

Hope this helps.

Pete

 

[Nebulous]

Pete_UK. THanks!

I tried that before posting, but I must have dome something wrong because I
was getting a circular reference error.

Some follow up questions, but first let me correct my initial question.

In reality, I'm shading M4 red if M4>0 and L4="x".

I accomplished this using your help by doing the following:
=AND(M4>0,L4="x")

Follow up questions:
1. L4 is shaded manually, buy I could easily make it a conditional
formatting. If I did that, how could I use that in my formula.

2. Am I correct that if I want all the cells in a column to have the same
conditional formatting (If M5>0 and L5="x" shade M5 red, and on down the
column), I would have to manually create conditions for each cell? In other
words, am I right that there's no way to copy conditional formatting formulas
for all cells in a column as there is for regular formulae?

3. What I REALLY need is much more complex that this, and I'm not sure if it
can be done. Columns E through L are all shaded different colors (again, I
could easily do that with conditional formatting, but right now it's manual).
What I really want is for a cell in M to adopt the color of whichever of
those columns contains an "x" in the same row. If there is an x in more than
one, I want it to adopt the shading of the right-most column (the highest
lettered column). Is that too complex for conditional formatting to handle?

Thanks again,

jb

 

[Pete_UK]

Glad it worked for you, though obviously you want more !!

In answer to your questions:

1. It all depends what kind of condition you want to set for L4.
Suppose you want L4 to be red if it contains "x", then you can set
that up with L4 selected and this time you can choose Cell Value Is,
then choose Equal To and put x in the next box, then click Format and
set the background colour to red. Then the setting for M4 would match
the setting for L4 (as long as M4 is greater than 0).

2. You do not need to set the conditions for one cell at a time.
Suppose you want M4 down to M50 to have that setting, then you should
highlight M4:M50 and have M4 as the active cell in that range before
clicking on Conditional Formatting, and if you use relative addressing
in the formula (as we did), then it will automatically apply to that
range when you have finished. Another way, if you have already set it
up for M4 is to select M4 then click on the Format Painter icon and
then select the range M5:M50.

If you wanted it to apply to columns as well as rows, then you would
need to highlight a block of cells (eg M4:R50) and use semi-absolute
addressing for L4 in the formula, i.e. the formula would need to be:

=AND(M4>0,$L4="x")

with M4 as the active cell, and then if L4 contained "x" all the cells
from M4 to R4 would show red if they contained a value greater than
zero.

3. Again it depends on what CF you would set for those cells in
columns E to L. You need to set up CF for each column of E to L in
turn, as you will have a different colour for each column. Then you
need to set the CF for column M. In XL2007 you can have many more
conditional formats than the 3 that was allowed in earlier versions,
and here you would need 8 conditions, each giving rise to a different
colour as set for the 8 columns. In the CF dialogue box, when you
click OK (once) after setting the format, you can then set others by
clicking the Add button. The CFs take precedence from the order in
which you set them up, so you would need to set up a formula with
columns L and M first, and use the colour that you have set for L.
Your second CF would have a formula that used K and M, and would pick
the colour set for column K. And so on.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

If the color in E3 is random and can be changed you would need to use VBA to
solve the problem of having E5 shaded like E3. But its possible.

 

[Herbert Seidenberg]

Excel 2007 Tables
Here is a VBA solution to the #3 task.
http://www.mediafire.com/file/tktqz0w2xuh/09_05_09.xlsm

 

[Nebulous]

Pete_UK:

Thanks again for your help.

Comments on your answers in order:
1. Because each column has a header row, my intention was to simply say "if
L1=[the heading] then color it red." That's easy to do and effectively makes
it a conditional format, because it will always be true. I could then do
similar for all columns E through L. But I still haven't understood a way to
use the color of the cell as a variable. I don't want to spend too much time
on that, though. I can do it differently without much effort.

2. Just FYI, Conditional Formatting doesn't seem to like relative addresses
in the "Applies To" field. It converts them all to absolute addresses. Since
I wanted this to apply to all cells in the M column other than the first, I
tried "M2:M1048576" but after I clicked OK it change it to "$M2:$M1048576."
It still works, however.

3. That works!

You're awesome. I greatly appreciate both your knowledge and your clear
communication!

jb

 

[Nebulous]

Herbert:

Thank you for taking the time to do this. I understand VBA just a little --
just enough to analyze someone else's code to see how it was done and perhaps
tweak it a bit, not enough to create my own.

I looked at your sample, and it seemed like it would work (though it
required clicking a buttin to change the cell -- I want it to happen
automatically in real time).

I did get it working with Conditional Formatting. Seems like it's much more
difficult to do than it should be -- but I'm thankful XL2007 doesn't have the
ridiculously small number o f allowed CFs as I remember from XL2003.

Thank you again,

Nebulous

 

[Pete_UK]

Well, thanks for feeding back, JB.

Pete

Pete_UK:

Thanks again for your help.

Comments on your answers in order:
1. Because each column has a header row, my intention was to simply say "if
L1=[the heading] then color it red." That's easy to do and effectively makes
it a conditional format, because it will always be true. I could then do
similar for all columns E through L. But I still haven't understood a wayto
use the color of the cell as a variable. I don't want to spend too much time
on that, though. I can do it differently without much effort.

2. Just FYI, Conditional Formatting doesn't seem to like relative addresses
in the "Applies To" field. It converts them all to absolute addresses. Since
I wanted this to apply to all cells in the M column other than the first,I
tried "M2:M1048576" but after I clicked OK it change it to "$M2:$M1048576.."
It still works, however.

3. That works!

You're awesome. I greatly appreciate both your knowledge and your clear
communication!

jb

Glad it worked for you, though obviously you want more !!

In answer to your questions:

1. It all depends what kind of condition you want to set for L4.
Suppose you want L4 to be red if it contains "x", then you can set
that up with L4 selected and this time you can choose Cell Value Is,
then choose Equal To and put x in the next box, then click Format and
set the background colour to red. Then the setting for M4 would match
the setting for L4 (as long as M4 is greater than 0).

2. You do not need to set the conditions for one cell at a time.
Suppose you want M4 down to M50 to have that setting, then you should
highlight M4:M50 and have M4 as the active cell in that range before
clicking on Conditional Formatting, and if you use relative addressing
in the formula (as we did), then it will automatically apply to that
range when you have finished. Another way, if you have already set it
up for M4 is to select M4 then click on the Format Painter icon and
then select the range M5:M50.

If you wanted it to apply to columns as well as rows, then you would
need to highlight a block of cells (eg M4:R50) and use semi-absolute
addressing for L4 in the formula, i.e. the formula would need to be:

with M4 as the active cell, and then if L4 contained "x" all the cells
from M4 to R4 would show red if they contained a value greater than
zero.

3. Again it depends on what CF you would set for those cells in
columns E to L. You need to set up CF for each column of E to L in
turn, as you will have a different colour for each column. Then you
need to set the CF for column M. In XL2007 you can have many more
conditional formats than the 3 that was allowed in earlier versions,
and here you would need 8 conditions, each giving rise to a different
colour as set for the 8 columns. In the CF dialogue box, when you
click OK (once) after setting the format, you can then set others by
clicking the Add button. The CFs take precedence from the order in
which you set them up, so you would need to set up a formula with
columns L and M first, and use the colour that you have set for L.
Your second CF would have a formula that used K and M, and would pick
the colour set for column K. And so on.

Hope this helps.

- Show quoted text -

 

[LJL]

Hi, Pete. Just wanted to let you know that I was just today (November 6)
searching for an answer for this issue. I tried the =AND function and it
worked perfectly. Thanks very much!

Lee