Banding with Conditional Formatting with Multiple Conditions
I can set up banding using Conditional Formatting fust fine. The problem I
am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
Perhaps you could paste a sample of all the CF formulas (for Conditions 1 to
3) used currently for a particular cell ? Think it would make it much easier for clarity, and for help to arrive -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
If you need more than 3 conditions, check out
http://xldynamic.com/xld.CFPlus.Dowmload.html -- HTH RP (remove nothere from the email address if mailing direct) "Max" wrote in message ... Perhaps you could paste a sample of all the CF formulas (for Conditions 1 to 3) used currently for a particular cell ? Think it would make it much easier for clarity, and for help to arrive -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
Hi Geremia
not sure what you want Bold & Italicized - all the "banded" rows or row #69. --so all odd rows banded, bold & italicized when f69D72 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72) -set formatting for banding colour & font bold & italicized 2nd condition =MOD(ROW()-1,2)+1<=1 --only row 69 bold & italicized 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69) -set formatting for banding colour & font bold & italicized 2nd condition - as above Hope this helps Cheers JulieD "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
Let me clarify what I want. I have a column of 68 cells. Each cell has a
date in it. I have another cell below it that always has the date it was 6 months ago in it. Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized. I also want to set up banding so that it's easier to follow the rows when I print out the spreadsheet. I am having trouble accomplishing both of those things at the same time. I guess I could put it this way: I want to shade all odd rows in a range. In all odd and even rows of my particular column, I want a bold and italicized font if the date in the cell is older than 6 months; and I want to keep the shading in each odd row. Does that make better sense? Thank you for your help and patience. "JulieD" wrote: Hi Geremia not sure what you want Bold & Italicized - all the "banded" rows or row #69. --so all odd rows banded, bold & italicized when f69D72 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72) -set formatting for banding colour & font bold & italicized 2nd condition =MOD(ROW()-1,2)+1<=1 --only row 69 bold & italicized 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69) -set formatting for banding colour & font bold & italicized 2nd condition - as above Hope this helps Cheers JulieD "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
Hi Geremia
what is the formula you're using in conditional formatting for "Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized." as it's difficult to tie this in with your first example. Cheers JulieD "Geremia Doan" wrote in message ... Let me clarify what I want. I have a column of 68 cells. Each cell has a date in it. I have another cell below it that always has the date it was 6 months ago in it. Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized. I also want to set up banding so that it's easier to follow the rows when I print out the spreadsheet. I am having trouble accomplishing both of those things at the same time. I guess I could put it this way: I want to shade all odd rows in a range. In all odd and even rows of my particular column, I want a bold and italicized font if the date in the cell is older than 6 months; and I want to keep the shading in each odd row. Does that make better sense? Thank you for your help and patience. "JulieD" wrote: Hi Geremia not sure what you want Bold & Italicized - all the "banded" rows or row #69. --so all odd rows banded, bold & italicized when f69D72 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72) -set formatting for banding colour & font bold & italicized 2nd condition =MOD(ROW()-1,2)+1<=1 --only row 69 bold & italicized 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69) -set formatting for banding colour & font bold & italicized 2nd condition - as above Hope this helps Cheers JulieD "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
JulieD,
I am not very good at explaining these things and I apologize for that. When I set up multiple conditions in Conditional Formatting, conditions 1 and 2 override condition 3, when condition 1 contains the 'banding' formula and conditions 2 and 3 contain a 'is greater' or 'is less' formula with a format of BOLD or BOLD ITALICIZED. So what happens is every other row is shaded, but where conditions 2 and 3 would normally apply, making the contents of the cell BOLD or BOLD ITALICIZED, the cell is shaded and the contents of the cell are neither BOLD or ITALICIZED. If I change Conditonal Formatting so that the 'banding' formula is condition 3, conditions 1 and 2 override condition 3 making the contents of the cell BOLD or BOLD ITALICIZED, but the cell is not shaded. If that still doesn't make sense, maybe you could take a peek at the spreadsheet. You can see it at home.comcast.net/~geremiadoan/ and its the link labeled Territory.xls. It's the second item from the top. My aim is to shade every other row, yet still be able to sort and keep this shading, as well as other conditional formatting that I already have set up. The copy that I have up on the website was saved prior to my having attempted the shading of odd numbered rows, but after having set up conditional formatting to change contents of cells to BOLD and BOLD ITALICIZED. Thank you for your help. You're the best! Geremia "JulieD" wrote: Hi Geremia what is the formula you're using in conditional formatting for "Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized." as it's difficult to tie this in with your first example. Cheers JulieD "Geremia Doan" wrote in message ... Let me clarify what I want. I have a column of 68 cells. Each cell has a date in it. I have another cell below it that always has the date it was 6 months ago in it. Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized. I also want to set up banding so that it's easier to follow the rows when I print out the spreadsheet. I am having trouble accomplishing both of those things at the same time. I guess I could put it this way: I want to shade all odd rows in a range. In all odd and even rows of my particular column, I want a bold and italicized font if the date in the cell is older than 6 months; and I want to keep the shading in each odd row. Does that make better sense? Thank you for your help and patience. "JulieD" wrote: Hi Geremia not sure what you want Bold & Italicized - all the "banded" rows or row #69. --so all odd rows banded, bold & italicized when f69D72 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72) -set formatting for banding colour & font bold & italicized 2nd condition =MOD(ROW()-1,2)+1<=1 --only row 69 bold & italicized 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69) -set formatting for banding colour & font bold & italicized 2nd condition - as above Hope this helps Cheers JulieD "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
Hi Geremia
i've had a look at the workbook and the best i can do for you is: apply banding to the odd rows AND if the date in column F is older than 6 months make all the text in that row go bold & italicized - i can't get JUST column F for the bold & italicized. if this is OK, select the rows that you want to apply the conditional formatting to, ensure that row 2 is the first row on the screen and the two conditions you need are" Condition 1: Formula is =AND(MOD(ROW()-1,2)+1<=1,$F2<=$D$72-240) set the format for bold & italicized and for the banding colour Condition 2: Formula is =MOD(ROW()-1,2)+1<=1 set the format for the banding colour. .... i've got a workbook i can send you if you get stuck. Hope this helps Cheers JulieD "Geremia Doan" wrote in message ... JulieD, I am not very good at explaining these things and I apologize for that. When I set up multiple conditions in Conditional Formatting, conditions 1 and 2 override condition 3, when condition 1 contains the 'banding' formula and conditions 2 and 3 contain a 'is greater' or 'is less' formula with a format of BOLD or BOLD ITALICIZED. So what happens is every other row is shaded, but where conditions 2 and 3 would normally apply, making the contents of the cell BOLD or BOLD ITALICIZED, the cell is shaded and the contents of the cell are neither BOLD or ITALICIZED. If I change Conditonal Formatting so that the 'banding' formula is condition 3, conditions 1 and 2 override condition 3 making the contents of the cell BOLD or BOLD ITALICIZED, but the cell is not shaded. If that still doesn't make sense, maybe you could take a peek at the spreadsheet. You can see it at home.comcast.net/~geremiadoan/ and its the link labeled Territory.xls. It's the second item from the top. My aim is to shade every other row, yet still be able to sort and keep this shading, as well as other conditional formatting that I already have set up. The copy that I have up on the website was saved prior to my having attempted the shading of odd numbered rows, but after having set up conditional formatting to change contents of cells to BOLD and BOLD ITALICIZED. Thank you for your help. You're the best! Geremia "JulieD" wrote: Hi Geremia what is the formula you're using in conditional formatting for "Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized." as it's difficult to tie this in with your first example. Cheers JulieD "Geremia Doan" wrote in message ... Let me clarify what I want. I have a column of 68 cells. Each cell has a date in it. I have another cell below it that always has the date it was 6 months ago in it. Using CF, I made it so that whenever the date in one of the 68 cells in the column is prior to 6 months ago, it becoms bold and italicized. I also want to set up banding so that it's easier to follow the rows when I print out the spreadsheet. I am having trouble accomplishing both of those things at the same time. I guess I could put it this way: I want to shade all odd rows in a range. In all odd and even rows of my particular column, I want a bold and italicized font if the date in the cell is older than 6 months; and I want to keep the shading in each odd row. Does that make better sense? Thank you for your help and patience. "JulieD" wrote: Hi Geremia not sure what you want Bold & Italicized - all the "banded" rows or row #69. --so all odd rows banded, bold & italicized when f69D72 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72) -set formatting for banding colour & font bold & italicized 2nd condition =MOD(ROW()-1,2)+1<=1 --only row 69 bold & italicized 1st condition =AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69) -set formatting for banding colour & font bold & italicized 2nd condition - as above Hope this helps Cheers JulieD "Geremia Doan" wrote: I can set up banding using Conditional Formatting fust fine. The problem I am having is that there are other conditions I need to have happen at the same time. For example, when cell F69 is greater than cell D72, I need it to be BOLD and ITALICIZED. When I use the banding formula in Conditional Formatting as the first Condition, it overrides the other conditions, so the cell is shaded, but not BOLD and ITALICIZED. If I make it the last Condition, the BOLD and ITALICIZED condition overrides the banding Condition. Does anyone know if there is a way to get around this problem? A lengthy formula, perhaps, to put into Conditional Formatting that will perform both functions? |
All times are GMT +1. The time now is 09:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com