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
---

Thank you. Yes, figured it out at last.

I had used column 'A' in this dialogue just as an example. The actual
column on my sheet is 'I'. Just realised that in my actual column A there
are dates aswell!

All works fine now. If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date, how could I change the formula to
accomodate this?

This perhaps is not as important. I am glad my original problem is now
solved.

Thanks again.
"Max" wrote:

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.

.. All works fine now.

Glad to hear that <g!

If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date,
how could I change the formula to accomodate this?

Sounds like you want to implement another 2 trigger conditions?

CF allows 3 trigger conditions to format cells, with an implicit 4th (ie
non-trigger condition) being the default format.

You might want to check out Bob Phillips'
CFPlus - Extended Conditional Formatter:
http://www.xldynamic.com/source/xld.....Download.html

Believe it can handle, to quote:
Multiple Conditional Formats, up to 30 in total for any range of cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote in message
...
Thank you. Yes, figured it out at last.

I had used column 'A' in this dialogue just as an example. The actual
column on my sheet is 'I'. Just realised that in my actual column A there
are dates aswell!

All works fine now. If, for example, a month needed to be entered that
was
beyond the +/- 1 month from today's date, how could I change the formula
to
accomodate this?

This perhaps is not as important. I am glad my original problem is now
solved.

Thanks again.

Dave,

Upon further thoughts re-visiting your Q:
If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date,
how could I change the formula to accomodate this?

Perhaps these revised cond format formulas would do it better ..

Condition 1:
=AND(DATE(YEAR(A1),MONTH(A1),1)<=DATE(YEAR(TODAY() ),MONTH(TODAY())-1,1),A1<"")
Format: Red fill

Condition 2:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY()) ,MONTH(TODAY()),1),A1<"")
Format: Amber fill

Condition 3:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY() ),MONTH(TODAY())+1,1),A1<"")
Format: Green fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Where the first formula had -1, which would show red if the month entered was
1 month less then today and the third formula had +1, would show green if the
month entered was 1 month more than today. That works great if today is
October and either September or November are entered. If August or December
are entered, then there is no colour change as these months are outside what
has been specified in the formula.

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?

If not, I will investigate multiple trigger conditions as suggested.

Once again, thank you.



"Max" wrote:

.. All works fine now.

Glad to hear that <g!

If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date,
how could I change the formula to accomodate this?

Sounds like you want to implement another 2 trigger conditions?

CF allows 3 trigger conditions to format cells, with an implicit 4th (ie
non-trigger condition) being the default format.

You might want to check out Bob Phillips'
CFPlus - Extended Conditional Formatter:
http://www.xldynamic.com/source/xld.....Download.html

Believe it can handle, to quote:
Multiple Conditional Formats, up to 30 in total for any range of cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote in message
...
Thank you. Yes, figured it out at last.

I had used column 'A' in this dialogue just as an example. The actual
column on my sheet is 'I'. Just realised that in my actual column A there
are dates aswell!

All works fine now. If, for example, a month needed to be entered that
was
beyond the +/- 1 month from today's date, how could I change the formula
to
accomodate this?

This perhaps is not as important. I am glad my original problem is now
solved.

Thanks again.

Thanks Roger.

I am using today() as the comparitive date.

"Roger Govier" wrote:

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
---

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?

Dave, posted this further response earlier (you might have missed it)

Upon further thoughts re-visiting your Q:
If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date,
how could I change the formula to accomodate this?

Perhaps these revised cond format formulas would do it better ..

Condition 1:
=AND(DATE(YEAR(A1),MONTH(A1),1)<=DATE(YEAR(TODAY() ),MONTH(TODAY())-1,1),A1<"")
Format: Red fill

Condition 2:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY()) ,MONTH(TODAY()),1),A1<"")
Format: Amber fill

Condition 3:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY() ),MONTH(TODAY())+1,1),A1<"")
Format: Green fill

The above should do it for you ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote:
Where the first formula had -1, which would show red if the month entered was
1 month less then today and the third formula had +1, would show green if the
month entered was 1 month more than today. That works great if today is
October and either September or November are entered. If August or December
are entered, then there is no colour change as these months are outside what
has been specified in the formula.

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?

If not, I will investigate multiple trigger conditions as suggested.

Once again, thank you.

Wow, that is spot on!

As this is a shared workbook, will the fact that it is shared prevent
anybody else from altering this CF?

"Max" wrote:

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?

Dave, posted this further response earlier (you might have missed it)

Upon further thoughts re-visiting your Q:
If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date,
how could I change the formula to accomodate this?

Perhaps these revised cond format formulas would do it better ..

Condition 1:
=AND(DATE(YEAR(A1),MONTH(A1),1)<=DATE(YEAR(TODAY() ),MONTH(TODAY())-1,1),A1<"")
Format: Red fill

Condition 2:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY()) ,MONTH(TODAY()),1),A1<"")
Format: Amber fill

Condition 3:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY() ),MONTH(TODAY())+1,1),A1<"")
Format: Green fill

The above should do it for you ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Dobson" wrote:
Where the first formula had -1, which would show red if the month entered was
1 month less then today and the third formula had +1, would show green if the
month entered was 1 month more than today. That works great if today is
October and either September or November are entered. If August or December
are entered, then there is no colour change as these months are outside what
has been specified in the formula.

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?

If not, I will investigate multiple trigger conditions as suggested.

Once again, thank you.

"Dave Dobson" wrote:
Wow, that is spot on!

You're welcome, Dave.

As this is a shared workbook, will the fact that it is shared prevent
anybody else from altering this CF?

Perhaps this was answered by Allllen in another earlier post?,

"Dave Dobson" wrote:
Easy when you know how! Thanks.

"Allllen" wrote:
Yes you can do it. You just need to set it up when the workbook is not
shared, then share it afterwards.

If you already have a shared file where you would like to do this, wait
until you think everyone is out of the file (you can even check it on the
sharing menu).
Then unshare it. It will save.
Then put your conditional format in place.
Then share it again and it will save again.

--
Allllen


"Dave Dobson" wrote:

It appears then, that I can not use conditional formatting in a shared
worksheet.

Is this the case?

I would like certain cells to be highlighted, based upon how far away the
date in that cell is, from today's date.

The worksheet must be shared.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---