Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Rules Manager Excel 2007
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Rules Manager Excel 2007
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 "Steved" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Rules Manager Excel 2007
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 "Steved" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Rules Manager Excel 2007
Thankyou
"OssieMac" wrote: 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 "Steved" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting conditional formatting rules | Excel Discussion (Misc queries) | |||
Suggestion: Excel 2007- New Conditional Formatting Rules and Optio | Excel Discussion (Misc queries) | |||
More than 3 rules of conditional formatting | Excel Discussion (Misc queries) | |||
Combine Conditional Formatting Rules formulae | Excel Discussion (Misc queries) | |||
I need 5 conditional formatting rules-excel only allows 3 | Excel Discussion (Misc queries) |