If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's valu
In Excel 2007...
If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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 |
#2
|
|||
|
|||
Conditional formatting based on Cell Value and another cell'svalu
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(E50,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 On Sep 4, 8:25*pm, Nebulous wrote: In Excel 2007... If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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 |
#3
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
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 M40 and L4="x". I accomplished this using your help by doing the following: =AND(M40,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 M50 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" wrote: 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(E50,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 On Sep 4, 8:25 pm, Nebulous wrote: In Excel 2007... If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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 |
#4
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
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(M40,$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 On Sep 4, 9:19*pm, Nebulous wrote: 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 M40 and L4="x". I accomplished this using your help by doing the following: =AND(M40,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 M50 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" wrote: 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(E50,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 On Sep 4, 8:25 pm, Nebulous wrote: In Excel 2007... If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's valu
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nebulous" wrote: In Excel 2007... If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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 |
#6
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
Excel 2007 Tables
Here is a VBA solution to the #3 task. http://www.mediafire.com/file/tktqz0w2xuh/09_05_09.xlsm |
#7
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
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 "Pete_UK" wrote: 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(M40,$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 On Sep 4, 9:19 pm, Nebulous wrote: 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 M40 and L4="x". I accomplished this using your help by doing the following: =AND(M40,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 M50 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" wrote: 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(E50,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 On Sep 4, 8:25 pm, Nebulous wrote: In Excel 2007... If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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- Hide quoted text - - Show quoted text - |
#8
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
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 "Herbert Seidenberg" wrote: Excel 2007 Tables Here is a VBA solution to the #3 task. http://www.mediafire.com/file/tktqz0w2xuh/09_05_09.xlsm |
#9
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
Well, thanks for feeding back, JB.
Pete On Sep 5, 10:08*pm, Nebulous wrote: 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 "Pete_UK" wrote: 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(M40,$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 On Sep 4, 9:19 pm, Nebulous wrote: 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. |
#10
|
|||
|
|||
Conditional formatting based on Cell Value and another cell's
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 "Pete_UK" wrote: 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(E50,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 On Sep 4, 8:25 pm, Nebulous wrote: In Excel 2007... If E50 and E3="x" I want E5 to be shaded red. How can I accomplish this? Even better would be: If E50 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 |
Thread Tools | |
Display Modes | |
|
|