Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
My worksheet has a number of columns. The second column is a sequence of
month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
Select B6:F66, with B6 as the active cell do formatconditional formatting
select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
Hum, didn't work for me. Using Excel 2007 and the example above, I:
Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
I don't have Excel 2007, but the problem is that with the "Equal to..."
option you have a used a rule equivalent to Excel 2003's "Cell Value Is", whereas you needed "Formula Is". I think the Excel 2007 option is labelled "Use a formula to determine which cells to format". -- David Biddulph "Al Avery" wrote in message ... Hum, didn't work for me. Using Excel 2007 and the example above, I: Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
I thought that "Equal to..." might be the problems as well. So I tried the
"Formula is..." option. In the formula box I entered "=vlookup(today()+30,B6:B66)". This formula points to the correct cell and, therefore, produces the correct result when used in a cell by itself, i.e., 09/24/2008. But this procedure also changed no formatting in the range. Stymied. - Al AVery "David Biddulph" wrote: I don't have Excel 2007, but the problem is that with the "Equal to..." option you have a used a rule equivalent to Excel 2003's "Cell Value Is", whereas you needed "Formula Is". I think the Excel 2007 option is labelled "Use a formula to determine which cells to format". -- David Biddulph "Al Avery" wrote in message ... Hum, didn't work for me. Using Excel 2007 and the example above, I: Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
In the "Formula is..." option in CF, you need a formula that returns a TRUE
or FALSE condition, so that a TRUE gives your conditional formatting and a FALSE doesn't. You had a condition =$B6=TODAY() Why not try that in your CF "Formula is...", as Peo suggested? -- David Biddulph "Al Avery" wrote in message ... I thought that "Equal to..." might be the problems as well. So I tried the "Formula is..." option. In the formula box I entered "=vlookup(today()+30,B6:B66)". This formula points to the correct cell and, therefore, produces the correct result when used in a cell by itself, i.e., 09/24/2008. But this procedure also changed no formatting in the range. Stymied. - Al AVery "David Biddulph" wrote: I don't have Excel 2007, but the problem is that with the "Equal to..." option you have a used a rule equivalent to Excel 2003's "Cell Value Is", whereas you needed "Formula Is". I think the Excel 2007 option is labelled "Use a formula to determine which cells to format". -- David Biddulph "Al Avery" wrote in message ... Hum, didn't work for me. Using Excel 2007 and the example above, I: Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
Sorry, I neglected to say that I had tried that suggestion, too. After I've
entered the condition and specified the formatting, the rule looks like this: Formula: =$B6=Today() Formatting: <yellow highlight Applies to: =$F$6:$F$66 Still produces no highlighting. If the following explanation correctly describes how this CF rule works, then I'm not surprised that there's no highlighting: Theoretical: The CF formula searches $B6:$B66 for the value TODAY(). When it locates that value, the condition is TRUE, as you said David. It then highlights the cell in $F$6:$F$66 whose row is the same as condition found. Practice: If this is accurate, the condition will never return a TRUE value since the values in column B are discrete dates that were generated by copying the top cell (B6) down to B66 using the lower-right-corner cross and choosing months from the smart tag. Is this correct thinking? Thanks, - Al Avery "David Biddulph" wrote: In the "Formula is..." option in CF, you need a formula that returns a TRUE or FALSE condition, so that a TRUE gives your conditional formatting and a FALSE doesn't. You had a condition =$B6=TODAY() Why not try that in your CF "Formula is...", as Peo suggested? -- David Biddulph "Al Avery" wrote in message ... I thought that "Equal to..." might be the problems as well. So I tried the "Formula is..." option. In the formula box I entered "=vlookup(today()+30,B6:B66)". This formula points to the correct cell and, therefore, produces the correct result when used in a cell by itself, i.e., 09/24/2008. But this procedure also changed no formatting in the range. Stymied. - Al AVery "David Biddulph" wrote: I don't have Excel 2007, but the problem is that with the "Equal to..." option you have a used a rule equivalent to Excel 2003's "Cell Value Is", whereas you needed "Formula Is". I think the Excel 2007 option is labelled "Use a formula to determine which cells to format". -- David Biddulph "Al Avery" wrote in message ... Hum, didn't work for me. Using Excel 2007 and the example above, I: Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
If you are wanting the same month, rather than the same date, try
=AND(MONTH($B6)=MONTH(TODAY()),YEAR($B6)=YEAR(TODA Y())) or =TEXT($B6,"yyyymm")=TEXT(TODAY(),"yyyymm") -- David Biddulph "Al Avery" wrote in message ... Sorry, I neglected to say that I had tried that suggestion, too. After I've entered the condition and specified the formatting, the rule looks like this: Formula: =$B6=Today() Formatting: <yellow highlight Applies to: =$F$6:$F$66 Still produces no highlighting. If the following explanation correctly describes how this CF rule works, then I'm not surprised that there's no highlighting: Theoretical: The CF formula searches $B6:$B66 for the value TODAY(). When it locates that value, the condition is TRUE, as you said David. It then highlights the cell in $F$6:$F$66 whose row is the same as condition found. Practice: If this is accurate, the condition will never return a TRUE value since the values in column B are discrete dates that were generated by copying the top cell (B6) down to B66 using the lower-right-corner cross and choosing months from the smart tag. Is this correct thinking? Thanks, - Al Avery "David Biddulph" wrote: In the "Formula is..." option in CF, you need a formula that returns a TRUE or FALSE condition, so that a TRUE gives your conditional formatting and a FALSE doesn't. You had a condition =$B6=TODAY() Why not try that in your CF "Formula is...", as Peo suggested? -- David Biddulph "Al Avery" wrote in message ... I thought that "Equal to..." might be the problems as well. So I tried the "Formula is..." option. In the formula box I entered "=vlookup(today()+30,B6:B66)". This formula points to the correct cell and, therefore, produces the correct result when used in a cell by itself, i.e., 09/24/2008. But this procedure also changed no formatting in the range. Stymied. - Al AVery "David Biddulph" wrote: I don't have Excel 2007, but the problem is that with the "Equal to..." option you have a used a rule equivalent to Excel 2003's "Cell Value Is", whereas you needed "Formula Is". I think the Excel 2007 option is labelled "Use a formula to determine which cells to format". -- David Biddulph "Al Avery" wrote in message ... Hum, didn't work for me. Using Excel 2007 and the example above, I: Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
Thanks, David. That got formatting going in the correct cell.
One more refinement - crossover dates. My column of date values has specific dates, as you've seen. I want to construct a condition that picks the cell in the next row down if today's date is greater than the one the condition is operating on. Like this, table repeated for ease: Row........Col B............Col F No.....Month Ending.... Value ----....--------------....--------- 32.....08/24/2008.....45,000 33.....09/24/2008.....45,250 34.....10/24/2008.....45,500 For today's date (09/21/2008), your logical AND formula will correctly pick cell B33. For the date 09/26/2008, two days beyond the date in cell B33, it will again correctly pick the same cell since that day's month and cell B33's month are the same and highllight cell F33. However, for this case, since the dates in column B are "Month Ending", I want the condition formula to pick cell B34 and then highlight the value in cell F34, $45,500. Thanks for your patience. - Al Avery "David Biddulph" wrote: If you are wanting the same month, rather than the same date, try =AND(MONTH($B6)=MONTH(TODAY()),YEAR($B6)=YEAR(TODA Y())) or =TEXT($B6,"yyyymm")=TEXT(TODAY(),"yyyymm") -- David Biddulph "Al Avery" wrote in message ... Sorry, I neglected to say that I had tried that suggestion, too. After I've entered the condition and specified the formatting, the rule looks like this: Formula: =$B6=Today() Formatting: <yellow highlight Applies to: =$F$6:$F$66 Still produces no highlighting. If the following explanation correctly describes how this CF rule works, then I'm not surprised that there's no highlighting: Theoretical: The CF formula searches $B6:$B66 for the value TODAY(). When it locates that value, the condition is TRUE, as you said David. It then highlights the cell in $F$6:$F$66 whose row is the same as condition found. Practice: If this is accurate, the condition will never return a TRUE value since the values in column B are discrete dates that were generated by copying the top cell (B6) down to B66 using the lower-right-corner cross and choosing months from the smart tag. Is this correct thinking? Thanks, - Al Avery "David Biddulph" wrote: In the "Formula is..." option in CF, you need a formula that returns a TRUE or FALSE condition, so that a TRUE gives your conditional formatting and a FALSE doesn't. You had a condition =$B6=TODAY() Why not try that in your CF "Formula is...", as Peo suggested? -- David Biddulph "Al Avery" wrote in message ... I thought that "Equal to..." might be the problems as well. So I tried the "Formula is..." option. In the formula box I entered "=vlookup(today()+30,B6:B66)". This formula points to the correct cell and, therefore, produces the correct result when used in a cell by itself, i.e., 09/24/2008. But this procedure also changed no formatting in the range. Stymied. - Al AVery "David Biddulph" wrote: I don't have Excel 2007, but the problem is that with the "Equal to..." option you have a used a rule equivalent to Excel 2003's "Cell Value Is", whereas you needed "Formula Is". I think the Excel 2007 option is labelled "Use a formula to determine which cells to format". -- David Biddulph "Al Avery" wrote in message ... Hum, didn't work for me. Using Excel 2007 and the example above, I: Selected F6:F66 On the Home tab, selected Condition Formatting Highlight Cell Rules Equal to... In the Equal to window, entered =$B6=TODAY() in the "Format cells that are EQUAL to" box Chose formatting Clicked Ok No cell formatting in the range F6:F66 changed. Even when I entered a specific cell in the date range,=$B$33 (in my example), no formatting changed Perhaps I'm doing something incorrectly. - Al Avery "Peo Sjoblom" wrote: Sorry, didn't notice that the dates are in B Select F6:F66, but use formula is =$B6=TODAY() -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Select B6:F66, with B6 as the active cell do formatconditional formatting select cell value is equal to and put =TODAY() in the box, select the formatting you want and click OK twice -- Regards, Peo Sjoblom "Al Avery" <Al wrote in message ... My worksheet has a number of columns. The second column is a sequence of month-ending dates that are one month apart. The 5th column has a series of values. I want the 5th column's cell highlighted whose date in the month-ending column includes to today's date. Here's an example (hope it comes out clealy enough with proportional font): Row Col B ... Col F No. Month Ending ... Value ---- -------------- --------- 32 08/24/2008 45,000 33 09/24/2008 45,250 34 10/24/2008 45,500 Since today's date is 09/20/2008, cell F33 should be highlighted. The actual array is B6:F66, without headers. Many thanks for any help, |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Non-Adjacent Columns
Wow! Is this really as difficult as I thought?
- Al |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Based on Value of Adjacent Cell | Excel Discussion (Misc queries) | |||
Conditional Formatting for adjacent cells | Excel Discussion (Misc queries) | |||
Help needed with conditional formatting and adjacent text | Excel Worksheet Functions | |||
conditional formatting adjacent cells | Excel Worksheet Functions | |||
How to do a conditional formatting based on an adjacent cell | Excel Discussion (Misc queries) |