Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-19-2010, 07:34 AM
Mandusin Mandusin is offline Excel Calculator Windows XP Excel Calculator Office 2007
Novice
Excel Calculator
 
Join Date: Sep 2010
Posts: 9
Mandusin is on a distinguished road
Default Excel Calendar

Hello. Today i downloaded a calendar template (http://office.microsoft.com/en-gb/te...402.aspx?CTT=1) and it's coded so that when you enter a date in certain column it highlights the date on calender in blue, could anyone help me with a formula so it turns dates on calender into other color when i type in different column ?

Thanks

Last edited by Mandusin; 12-19-2010 at 10:48 AM.
Reply With Quote
  #2  
Old 12-21-2010, 02:03 PM
BjornS BjornS is offline Excel Calculator Windows Vista Excel Calculator Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hi,
try this (sorry for any translation mistakes, I use a Swedish Excel version).

Put some dates in column AE (for example). Lets say you reserve AE2:AE99 for this. Enter 1st of June 2011 (in your date format) in cell AE2 (just for test purpose later)

Mark cells C12:Y44. Select menu "Start / Conditional formatting / Create new rule". Select the last alternative "Determine what cells should be formatted using a formula". Enter this formula:
=IF(VLOOKUP(C12,$AE$2:$AE$99,1,FALSE())=C12,TRUE() )
and select an ugly colour to highlight your special dates.

Press OK and now you should see 1st of June highlighted

Kind regards
Bjorn
Reply With Quote
  #3  
Old 12-24-2010, 06:58 AM
Mandusin Mandusin is offline Excel Calculator Windows 7 64bit Excel Calculator Office 2010 64bit
Novice
Excel Calculator
 
Join Date: Sep 2010
Posts: 9
Mandusin is on a distinguished road
Default

Unfortunately it didn't work, i get an error when i type the formula saying the formula contains an error :P
Perhaps there's a typo in your formula or did I do something wrong.
Thanks for the reply.
Reply With Quote
  #4  
Old 12-24-2010, 10:37 AM
Kimberly Kimberly is offline Excel Calculator Windows 7 Excel Calculator Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Did you successfully find Conditional Formatting?

If the cell where you want to type the date to highlight is A1, and the cell that is the upper left corner of the cells that contain dates is C5, then select the range (C5 through whatever) and use this formula in the Conditional Formatting dialog box:

=C5=$A$1
Reply With Quote
  #5  
Old 12-24-2010, 03:51 PM
BjornS BjornS is offline Excel Calculator Windows Vista Excel Calculator Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hi, one problem could be language specific settings. Can you try to copy any of the other (already existing) formulas for conditional formating,m while standing in a date cell and paste it into the forum. Preferrably choose a formula that has this VLOOKUP function (I can't describe more accurate at the moment, no Excel on this computer).

Kind regards
Bjorn
Reply With Quote
  #6  
Old 12-25-2010, 02:06 AM
Mandusin Mandusin is offline Excel Calculator Windows 7 64bit Excel Calculator Office 2010 64bit
Novice
Excel Calculator
 
Join Date: Sep 2010
Posts: 9
Mandusin is on a distinguished road
Default

Thanks for the help, I didn't notice I can edit formulas in conditional formatting, so when I was looking to copy the formula for you I just tweaked it a bit and it worked.

Thanks !
Reply With Quote
  #7  
Old 12-25-2010, 07:34 AM
Kimberly Kimberly is offline Excel Calculator Windows 7 Excel Calculator Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

I should have looked at the template before posting (I would have realized there is a column to enter dates, not just a cell), but here is the formula, just a simple VLOOKUP is all that is needed. Assuming the calendar range begins with C5, and the dates will be entered in A1:A30...
=VLOOKUP(C5,$A$1:$A$40,1,0)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Age Calculator in MS Outlook 2002 SP3 turns Outlook 0 06-15-2010 12:26 AM
Excel Calculator Opening multiple Excel files within the same Excel window. lost9471 Excel 2 05-01-2010 01:57 PM
How do I use an Excel form to populate and Excel spreadsheet apostht Excel 0 05-21-2006 11:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:51 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft