Conditional formatting to change colour of Rows

I want to use conditional formatting to change the colour of the entire row when the date on A1 is any date in September (01/09/09 to 30/09/09).

Can somebody provide me the formula for this?

The "Formula Is" option for conditional formatting just needs something that resolves to true or false; think of the first part of an IF statement.

Here's one possible solution: =AND($A$1>VALUE("09/01/2009"),$A$1<VALUE("09/30/2009"))

Note that you have to make A1 into an absolute reference: $A$1

You could also put your two dates into two other cells and use absolute references in your conditional formatting to use them. This would prevent having to do "low-level" edits to your spreadsheet if you need it in the future.

Originally Posted by royUK

Which row?

Could be any row. The condition is that the first cell (the date cell) is a date in August then that entire row should change to a particular colour.

Thanks masteff.

But it does not seem to work.

Opps... should have been <= and >=. That's what I get for not testing the end points.

=AND($A$1>VALUE("09/01/2009"),$A$1<VALUE("09/30/2009"))

Originally Posted by masteff

Opps... should have been <= and >=. That's what I get for not testing the end points.

=AND($A$1>VALUE("09/01/2009"),$A$1<VALUE("09/30/2009"))

Sorry. Can you post the actual code to be used, because you have posted the same code that did not work.

Thanks for your help. Much appreciated.

Sorry, pasted the wrong bit. Should be: =AND($A$1>=VALUE("09/01/2009"),$A$1<=VALUE("09/30/2009"))

• Highlight the rows or range of cells that you want to apply the conditional formatting to
• On the menu, go to Format, Conditional Formatting
• In Condition 1, change "Cell Value Is" to "Formula Is"
• Copy and paste the formula into the box to the right of that
• Click the "Format..." button
• Click the patterns tab and pick a color of your choice
• Ok, Ok

Originally Posted by masteff

Sorry, pasted the wrong bit. Should be: =AND($A$1>=VALUE("09/01/2009"),$A$1<=VALUE("09/30/2009"))

• Highlight the rows or range of cells that you want to apply the conditional formatting to
• On the menu, go to Format, Conditional Formatting
• In Condition 1, change "Cell Value Is" to "Formula Is"
• Copy and paste the formula into the box to the right of that
• Click the "Format..." button
• Click the patterns tab and pick a color of your choice
• Ok, Ok

Excellent masteff. It worked. Thanks for your time and efforts.

DonkeyOte, thank you as well for the solution. Take care guys.

Originally Posted by DonkeyOte

=TEXT($A1,"MMYY")="0909"

Ah ha! That's the elegant solution!