How to colour cells by Month.

[AndyRoo119]

Hi All,
I have, what I think, is simple problem. But for the life of me I cannot work out the logic.

I have a simple list of 24 birth dates in column A, A1 to A24, formatted as dd mmm yyyy.
In Column B, I have the corresponding dates, formatted as just mm. Just giving the month number.
I would like the dates cells in column A to alternate colours, say light green and darker green, based on each month.
I used conditional formatting initially and it worked fine as long as there was a date in each month.
However, if there there are, for example, no dates in April, then March and May are the same light colour.

I've tried For-Next loops in VBA but cannot work it out successfully.
If someone could point me in the right direction I would be very gratefull.

Thanks

[AliGW]

Have you tried two conditional formatting rules:

=ISODD(ROW())

and:

=ISEVEN(ROW())

[AndyRoo119]

Hi ALIgw,

I understand that, but, if there is a date of 10 January 1978 (M01) - with a light background
No dates in February (M02) - dark background,
Then a date of 8 March 1998 (M03) - light background,
both adjacent cells, the January and March date will be the same light colour.

So that people can readily see the difference between January and March, I want March to now be the dark background so as to contrast the colour January then,
If in the future, a date is added in February, that date cell will become the dark colour and March will become the light colour, and so on.

Perhaps I should have said that the dates in column A are dynamic. Rows are added or deleted as required.
Sorry if i didn't make it clear.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[AndyRoo119]

Hi again,
Have attached the sample.

[AliGW]

You didn't mock up how you want it to look.

[ChemistB]

Changed Column B to =MONTH(A1) and formatted to custom 00

Selecting A1:B24: Conditional Formatting> New Rule > Using Formula

Please Login or Register  to view this content.

Next rule

Please Login or Register  to view this content.

Is that what you are looking for?

[AndyRoo119]

Thanks ChemistB
Just what I was looking for.
(I would never have worked out that conditional formatting)

Thanks once again.