A column has different months entered. Generally these will be a month
either side of the current month, or the current month itself. I would like
to use conditional formatting to change the cell colour to:

Green - Next month
Amber - Current month
Red - Last month and beyond.

How can I do this without specifying +/- number of days, as there are a
different number of days in each month?

Assuming real dates in col A,

Select col A (with A1 active), then apply the CF's formulas / fills:

Condition 1
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())-1,A1<"")
Format: Red

Condition 2
Formula is:
=AND(MONTH(A1)=MONTH(TODAY()),A1<"")
Format: Amber

Condition 3
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())+1,A1<"")
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote:
A column has different months entered. Generally these will be a month
either side of the current month, or the current month itself. I would like
to use conditional formatting to change the cell colour to:

Green - Next month
Amber - Current month
Red - Last month and beyond.

How can I do this without specifying +/- number of days, as there are a
different number of days in each month?

Ok, that worked for a specific cell with a real date.

What if I wanted to apply this to a whole column? My column has a header in
A1 and the column will be increased/decreased on a daily basis.

eg: Today there is data in A2:A52. Tomorrow there may be data in A2:A58.
Next day may only be A2:A46 etc.

The data is not a real date. "September" will be written in that cell. I
could, however, enter a real date.

"Max" wrote:

Assuming real dates in col A,

Select col A (with A1 active), then apply the CF's formulas / fills:

Condition 1
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())-1,A1<"")
Format: Red

Condition 2
Formula is:
=AND(MONTH(A1)=MONTH(TODAY()),A1<"")
Format: Amber

Condition 3
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())+1,A1<"")
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote:
A column has different months entered. Generally these will be a month
either side of the current month, or the current month itself. I would like
to use conditional formatting to change the cell colour to:

Green - Next month
Amber - Current month
Red - Last month and beyond.

How can I do this without specifying +/- number of days, as there are a
different number of days in each month?

Hi

Yes, you can apply the CF to as many cells in the column as you wish.

Where you have September, enter 01/09/2006 (or your Regional format for
01 Sep 2006) and then format that cell
FormatCellsNumberCustom mmmm
and what will be displayed is September.

--
Regards

Roger Govier


"Dave Dobson" wrote in message
...
Ok, that worked for a specific cell with a real date.

What if I wanted to apply this to a whole column? My column has a
header in
A1 and the column will be increased/decreased on a daily basis.

eg: Today there is data in A2:A52. Tomorrow there may be data in
A2:A58.
Next day may only be A2:A46 etc.

The data is not a real date. "September" will be written in that
cell. I
could, however, enter a real date.

"Max" wrote:

Assuming real dates in col A,

Select col A (with A1 active), then apply the CF's formulas / fills:

Condition 1
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())-1,A1<"")
Format: Red

Condition 2
Formula is:
=AND(MONTH(A1)=MONTH(TODAY()),A1<"")
Format: Amber

Condition 3
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())+1,A1<"")
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote:
A column has different months entered. Generally these will be a
month
either side of the current month, or the current month itself. I
would like
to use conditional formatting to change the cell colour to:

Green - Next month
Amber - Current month
Red - Last month and beyond.

How can I do this without specifying +/- number of days, as there
are a
different number of days in each month?

"Dave Dobson" wrote:
Ok, that worked for a specific cell with a real date.
What if I wanted to apply this to a whole column? ..

If you had selected the entire col A before applying the CF as suggested in
my earlier response, re line:
Select col A (with A1 active), then apply the CF's formulas / fills
it would have worked for the entire col A ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Ok. The cell above the one just entered now changes colour.

You say "Select col A": for this I clicked on 'A' to highlight the whole
column. Did I misinterpret? Please also verify what you mean by 'Active'.
How can a cell be active if the whole column is highlighted?

Thanks.

"Max" wrote:

"Dave Dobson" wrote:
Ok, that worked for a specific cell with a real date.
What if I wanted to apply this to a whole column? ..

If you had selected the entire col A before applying the CF as suggested in
my earlier response, re line:
Select col A (with A1 active), then apply the CF's formulas / fills
it would have worked for the entire col A ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

If you selected the entire col A by clicking on the col header "A", then
it's okay, A1 will be active cell (the highlighted "white" cell within the
selection). But one could have also selected col A by selecting A65536
first, then do a CTRL+SHIFT+Arrow Up, in which case A65536 would be the
active cell. The earlier cond format formulas given were to be applied to
col A with A1 active, not with A65536 active. Hope this clarifies.

Were you able to get the CF applied to the entire col A?
I'm still not sure from your response.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote in message
...
Ok. The cell above the one just entered now changes colour.

You say "Select col A": for this I clicked on 'A' to highlight the whole
column. Did I misinterpret? Please also verify what you mean by
'Active'.
How can a cell be active if the whole column is highlighted?

Thanks.

Hi Dave

If you click on the column header letter to select the whole column, the
active cell is automatically the first cell in that column.

If you clicked on say cell A4, then held your left mouse button down as
you proceeded down the column, on release of the mouse button, you would
have marked a range of cells, but A4 would be the active cell

Are you using the date entered in Cell A1 as your comparative date,
rather than Today() ?
If so, you will need to amend each of Max's formulae, but ensure that
you use an absolute cell reference for the second occurrence of A1 in
the formulae.

=AND(MONTH(A1)=MONTH($A$1)-1,A1<"")

This will make Cell A1 itself, Amber.
If you don't want that, having formatted the whole column, just mark
cell A1 and remove conditional formatting.

--
Regards

Roger Govier


"Dave Dobson" wrote in message
...
Ok. The cell above the one just entered now changes colour.

You say "Select col A": for this I clicked on 'A' to highlight the
whole
column. Did I misinterpret? Please also verify what you mean by
'Active'.
How can a cell be active if the whole column is highlighted?

Thanks.

"Max" wrote:

"Dave Dobson" wrote:
Ok, that worked for a specific cell with a real date.
What if I wanted to apply this to a whole column? ..

If you had selected the entire col A before applying the CF as
suggested in
my earlier response, re line:
Select col A (with A1 active), then apply the CF's formulas /
fills
it would have worked for the entire col A ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---