Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting, Excel 2007
I am trying to copy conditional formatting in Excel 2007, but it doesn't seem
to work the same way that I remember in 2003. Or maybe I'm doing something wrong. I have a date in A1, which just displays the month. Then in B1:B31 I have the numbers 1 through 31 for days of the month, and C1:C31, blank boxes for data. If the month has less than 31 days, I want to "hide" the extra days by formatting those cells blue to match the surrounding cells. For example, if the month in A1 is Feb-2009, then B29:C31 should all have the special formatting. I started by selecting B31:C31, and in the conditional formatting dialog, used the formula: =B31DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) That seems to work well, but when I copy the format to the other cells, it doesn't copy correctly. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting, Excel 2007
I set up a worksheet as you have
Selected B1:B31 and used this Conditional Formatting rule: =B1DAY(DATE(YEAR($A$1),MONTH($A$1)+1,1)-1) Note the absolute references to A1 I chose to give cells a yellow fill for testing purposes. It worked best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Horatio J. Bilge, Jr." wrote in message ... I am trying to copy conditional formatting in Excel 2007, but it doesn't seem to work the same way that I remember in 2003. Or maybe I'm doing something wrong. I have a date in A1, which just displays the month. Then in B1:B31 I have the numbers 1 through 31 for days of the month, and C1:C31, blank boxes for data. If the month has less than 31 days, I want to "hide" the extra days by formatting those cells blue to match the surrounding cells. For example, if the month in A1 is Feb-2009, then B29:C31 should all have the special formatting. I started by selecting B31:C31, and in the conditional formatting dialog, used the formula: =B31DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) That seems to work well, but when I copy the format to the other cells, it doesn't copy correctly. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting, Excel 2007
That wasn't working either, until I discovered that the order in which I
selected the cells made a difference. When I clicked on B31 and dragged up to include the rest of the cells, the conditional format didn't work. When I clicked on B1 and dragged down to include the other cells, it works as expected. Thanks. ~ Horatio "Bernard Liengme" wrote: I set up a worksheet as you have Selected B1:B31 and used this Conditional Formatting rule: =B1DAY(DATE(YEAR($A$1),MONTH($A$1)+1,1)-1) Note the absolute references to A1 I chose to give cells a yellow fill for testing purposes. It worked best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Horatio J. Bilge, Jr." wrote in message ... I am trying to copy conditional formatting in Excel 2007, but it doesn't seem to work the same way that I remember in 2003. Or maybe I'm doing something wrong. I have a date in A1, which just displays the month. Then in B1:B31 I have the numbers 1 through 31 for days of the month, and C1:C31, blank boxes for data. If the month has less than 31 days, I want to "hide" the extra days by formatting those cells blue to match the surrounding cells. For example, if the month in A1 is Feb-2009, then B29:C31 should all have the special formatting. I started by selecting B31:C31, and in the conditional formatting dialog, used the formula: =B31DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) That seems to work well, but when I copy the format to the other cells, it doesn't copy correctly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting in Excel 2007 | Excel Discussion (Misc queries) | |||
conditional formatting in Excel 2007 - help, please! | New Users to Excel | |||
Conditional Formatting in Excel 2007 | Excel Worksheet Functions | |||
Using conditional formatting in Excel 2007 | New Users to Excel | |||
Conditional formatting in Excel 2007 | Excel Discussion (Misc queries) |