Conditional Formatting Rules Manager Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steved - 17 Sep 2007 02:52 GMT

Hello from Steved

In Conditional Formatting Rules Manager is it possible to ask to look for
6-feb and color the text Red and the background White I use this formula to
build my Calendar
{=IF(MONTH(D3-DAY(D3)+1-WEEKDAY(D3-DAY(D3)+1)+Mtx)=MONTH(D3),D3-DAY(D3)+1-WEEKDAY(D3-DAY(D3)+1)+Mtx,"")}

Others to look for would be the first Monday in June ( Holiday ) and the
second to last Monday in October ( Holiday )

Thankyou.

Reply to this Message

OssieMac - 17 Sep 2007 05:30 GMT

Hi Steve,

If you create a table somewhere with a list of all the holidays then you can
use the VLOOKUP function for the conditional formatting of the holidays.

If you select the entire range for the conditional format the formula will
be something like this:-

=VLOOKUP(A1,$L$1:$L$10,1,FALSE)
Where A1 is the first cell of the selection and $L$1:$L$10 is the list of
holidays.

Note the relative and absolute addressing in the formula. A1 is relative and
the range $L$1:$L$10 for the holiday list is absolute.

Regards,

OssieMac

> Hello from Steved
>
[quoted text clipped - 7 lines]
>
> Thankyou.

Reply to this Message

OssieMac - 17 Sep 2007 05:40 GMT

Hi again Steve,

I should have added that I build my calendars using the actual dates. I
insert the 1/1/2007 for the first day on the calendar and consecutive dates
are simply the previous date + 1.

I then custom format the cells as "d" and they only display the date number.
This method makes it easy when creating holiday lists because they can be
created with actual dates the formula only has to look up a date. I am not
sure that the VLOOKUP will work the way you have created your calendar.

Regards,

OssieMac

> Hello from Steved
>
[quoted text clipped - 7 lines]
>
> Thankyou.

Reply to this Message

Steved - 17 Sep 2007 07:58 GMT

Thankyou

> Hi again Steve,
>
[quoted text clipped - 22 lines]
> >
> > Thankyou.

Reply to this Message