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.

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.

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.