Colouring cell if it has a specific date

[Shivam.Shah]

Hi,

I have a column for each month where the dates are entered on rows. Is
it possible I can color a cell if the date entered in a cell for that
month is 15 days past the end of the month. Like, if in a January
column I enter a date on one of the rows as Feb.20, then the cell
fills with red colour.

I tried doing it using conditional formatting, but don't know how to
say 15 days past end of the month?

Any help will be appreciated! Thanks very much!

Shivam

 

[T. Valko]

What version of Excel are you using?

 

[Shivam.Shah]

Excel 2003

What version of Excel are you using?

 

[Kidaeshus]

I tried doing it using conditional formatting, but don't know how to

say 15 days past end of the month?

This would be a good place to use static data within your column
headings. I'm going to assume that you have twelve columns (Jan,
Feb...Nov, Dec) with headings across A1 to L1 and data starting in the
second row. Instead of simply Typing in January, February, etc, put in
a value like 1/1/2010, 1/2/2010, etc and provide a custom format as
either "mmm" or "mmmm" depending upon your own preference.

Now your conditional formatting will be by formula using the EOMONTH
function (End Of Month). The data in column A will have:

=IF(A2>EOMONTH($A$1,0)+15, TRUE, FALSE)

.... the data in column B will use:

=IF(B2>EOMONTH($B$1,0)+15, TRUE, FALSE)

.... etc.

I hope this clear enough.

HTHs.

 

[Pete_UK]

Note that to use the EOMONTH function in Excel 2003 you will need to
have the Analysis Toolpak installed.

Hope this helps.

Pete

 

[T. Valko]

What version of Excel are you using?

=IF(A2>EOMONTH($A$1,0)+15, TRUE, FALSE)

Since the OP is using Excel 2003 you can't use functions from the Analysis
ToolPak add-in (EOMONTH) for conditional formatting. Excel will "complain"
about not being able to reference other sheets or other workbooks.

Luckily, the work-around for the EOMONTH function is pretty easy.

=A2>DATE(YEAR(A$1),MONTH(A$1)+1,0)+15

 

[T. Valko]

See my reply to Kidaeshus in this thread.

--
Biff
Microsoft Excel MVP


Excel 2003

 

[Shivam.Shah]

Hi Biff and Kidasheus,

I tried that and it works, but the thing is, the date in my column is
entered automatically by an macro (upon entering data in the cell to
it's left)...so even though I put a conditional format on the cell,
after the date is entered by the macro, it didn't change colour.

So, is it possible to use a macro that can look in a column and then
colour a date if it is 15 days past the end of the current month?

Thanks for all your help!

Shivam

 

[Shivam.Shah]

Sorry about that, sorted it out. There was a problem with my other
macro in the sheet...everything works perfect now!

Thanks a lot!!

 

[T. Valko]

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Sorry about that, sorted it out. There was a problem with my other
macro in the sheet...everything works perfect now!

Thanks a lot!!