Changing one cell colour by inserting date in another cell

[Guest]

Following is an excample of my spread sheet:

A B C D E F G
H
1 Start Date : MONTH
2 : F : S : S : M : T : W : T
:
3 : 1 : 2 : 3 : 4 : 5 : 6
: 7 :
------------------------------------------------------------------------------
4 ? : ? : : : : :
: :

I would like cell B4 (1st day of Sept.) to change to a colour when I enter
the date (01/09/06) in cell A4.
Is this possible?
Many thanks

 

[Carim]

Hi,

Use conditionnal formatting in B4
Formula Is and enter formula =MATCH(date(2006,9,1),$A4,0)
and then select Format and Pattern which you want ...

HTH
Cheers
Carim

 

[Guest]

Thank you very much Carim, yes it worked. However, is there a way that I
could copy the same conditional format to the rest of the cells representing
every day of the month without having to format every cell individually?

 

[Carim]

Hi,

A quick but not ideal way would be, to have your formula replaced by :
=IF(isnumber(A1),1,0)

But ideally, it would be better to have the formula
=IF(isdate(A1),1,0)
and add in one of your modules :

Function IsDate(Var As Variant) As Boolean
' from Chip Pearson
IsDate = VBA.IsDate(Var)
End Function


HTH
Cheers
Carim