• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calendar highlighting

A

ahhhmed

Member
Hi all,


In an excel calendar that depends on formulas to calculate dates, e.g. http://chandoo.org/wp/2012/01/02/picture-calendar-template/


I need to highlight the cell that shows the current date. I tried conditional formatting with a formula =TODAY() and chose to fill the cell red, the result was all the cells were filled red.


How can I highlight only the cell that shows today's date?
 
You need to have your CF formula compare TODAY() to something. Otherwise, it just says

=TODAY()

=40934

=TRUE (as any value greater than 0 is considered TRUE)


Assuming there's a cell in the calendar that contains a date, I would imagine the formula looks like:

=TODAY()=A2
 
Thanks Luke,

Actually I used the formula A2=today() , but as i said, all the calendar was formatted. The problem is that the cells contain just numbers and not dates. I mean they contain the numbers from 1 to 31 for the month of January and so on. They do not contain dates.
 
Can you do something like:

=AND(DAY(TODAY())=DayNumber,TEXT(TODAY(),"mmmm")=CellWithMonth)
 
@Luke M,

Please check this page. Can the code be shortened?


http://www.teachexcel.com/free-excel-macros/m-145,create-calendar-12-month-excel-macro-free.html
 
Hi, ahhhmed!

I gave a look at your link and I must tell you that the implementation of chandoo.org's one is much smoother and cleaner that the code of your link. Maybe you can shorten it deleting double spaces and blank lines...

Besides, I downloaded the pictorial-calendar-v1.xlsm, chose sheet Calc, selected range C9:W10, typed as formula in conditional formatting =C9=TODAY(), set format to yellow background and it worked perfectly, so... what couldn't you do with that workbook?

Regards!
 
Hi SirJB7

Thank you for your interest and reply. YES, the formula works well in the pictorial calendar, but this is not the calendar that I depend on in my work. I have uploaded my calendar for you to try to set the highlighting formula. I tried but failed.


http://www.4shared.com/office/TYXfX6gC/2010-calendar.html
 
Hi, ahhhmed!

Four things to modify about conditional formatting:

1) delete rule =C3=TODAY(), it useless

2) modify rule =LEFT(D$3,1)="S" applied to =$C$4:$AR$15 to =LEFT(C$3,1)="S" applied to =$C$3:$AR$15, it highlighting incorrectly Friday and Saturday instead of Saturday and Sunday (it's asking for left character S, isn't it?)

3) add rule =FECHA(Outline!$D$3;FILA()-3;C4)=HOY() -----> in english: =DATE(Outline!$D$3,ROW()-3,C4)=TODAY() applied to =$C$4:$AR$15, I think it's what you asked for

4) uncheck stop-if-true checkbox in all rules

One thing about formatting:

5) change cell format from C3:AR3 to light grayed backcolor (can copy any W-ednesday- cell format to all range), because you highlighted Friday fixed as it was Saturday or Sunday

Regards!
 
Hi SirJB7,


I liked your amendments.


The holidays in my country are Fri. and Sat. so they are highlighted grey.


Next, when I put the rule =DATE(Outline!$D$3;ROW()-3;C4)=TODAY() as a new rule in CF, I got a message saying I cannot use a reference to another worksheet for CF in this case Outline!


Regards.
 
Hi, ahhhmed!

I didn't know about your holidays, I just followed up what the rules said.

You'll have to change the rule:

=LEFT(D$3,1)="S"

to:

=OR(D$3="F",D$3="Sa")

And you've left ";" in the formula instead ",". Check the english version I posted, you mixed english function names with my spanish local separators.

Regards!
 
Hi SirJB7,

As for the separators in the function, Excel 2007 that I use does not accept ( , ) as a separator and gives me an error message so that i had to change it to ( ; ).


Yet, the program still does not accept the refernce to Outline! for CF

Any solutions please?

Thanx
 
Hi, ahhhmed!

Here's your uploaded file, modified as explained before:

http://www.2shared.com/document/OAwwJwTO/2010-calendar_-_Calendar_highl.html

Today's date is highlighted in back yellow, weekends set to Fri&Sat, and when you open it in your non-spanish Excel you'll get, as well as formulas translated, the right separator characters.

Just advise if any trouble.

Regards!
 
Gracias SirJB7,


I downloaded the file. It does NOT contain any CF and the today's cel is not highlighted. You may have uploaded an unamended file. Would you pls send it again?

Thanx
 
Hi, ahhhmed!

Sorry, but the highlighted format wasn't compatible with the file format .xls, that's why it dropped when saving the workbook. Here it is in .xlsm format (Excel 2007 & 2010):

http://www.2shared.com/file/nj7ZBVK2/2010-calendar_-_Calendar_highl.html

Regards!
 
Hi, ahhhmed!

I opened the file in my PC and it highlights today. I can't download from 2shared, it keeps waiting. I'll try and check later, of if you wish post an email so I can send it directly to you.

Regards!
 
Thanx SirJB7

Here is my email

ahmedmallook@hotmail.com

Does it matter if I am using excel 2007? Do I have to use 2010?
 
Thanks a lot SirJB7,


Can I do the same to the calendar in the outline worksheet? What formula would I use?


NB the file worked well on excel 2010 not 2007.


Regards
 
Hi, ahhhmed!


The conditional formatting formula "is and should be" this:

=SI(ESERROR(FECHA($D$3;(ENTERO((FILA()+2)/9)-1)*3+(ENTERO((COLUMNA()+6)/8)-1)*3+1;C11));FALSO;FECHA($D$3;(ENTERO((FILA()+2)/9)-1)*3+(ENTERO((COLUMNA()+6)/8)-1)*3+1;C11)=HOY()) -----> in english: =IF(ISERROR(DATE($D$3,(INT((ROW()+2)/9)-1)*3+(INT((COLUMN()+6)/8)-1)*3+1,C11)),FALSE,DATE($D$3,(INT((ROW()+2)/9)-1)*3+(INT((COLUMN()+6)/8)-1)*3+1,C11)=TODAY())


It transforms ranges from 5 to 12 in 1, 14 to 21 in 2, 23 to 30 in 3... for rows. And 2 to 8 in 1, 10 to 16 in 2, 18 to 24 in 3... for columns.

Then it does (newrow-1)*3+(newcol-1)*3+1 and that's your month.

Hence, it builds date as ($D$3, <month>, day).


And it "should and must" let you apply highlighting format.


But, for an unknown reason it "doesn't work" as formula conditional formatting condition even it "yes works" in a separate cell (see AC11 in correspondance with C11) for today's date.

I can't find out this behaviour source, maybe you can try to discover by yourself or with other people aid... If so, please let me know what was wrong.


Here's the file with the CF intended to be applied, and in cell AC11 the check for C11 (today's date) behaviour: same formula ,just a "-26" correction factor for columns offset difference between C and AC.

http://www.2shared.com/file/a2UC30Un/2010-calendar_-_Calendar_highl.html

I've emailed it to you as well. Good luck.


Regards!
 
Hi SirJB7,

Thank you very much for the good try. Yes, it is weired action though it says it is OK when I do formula evaluation for AC11.


I tried something else. I removed the cell merge for the month names.

Please try it and see what happens.


This issue turns to be interesting and challenging, but it is useful to have such a result.


waiting for your generous respond, I am

Ahmed
 
Hi, ahhhmed!

I don't understand what you meant with "I removed the cell merge for the month names".

Regards!
 
Hi SirJB7,


You see for example Cell B5 in the Outline worksheet contains the month of January and it is actuall a merged cell (B5 to H5) . I removed the merging of these cells so that I have individual cells B5 C5 D5 and so on. I did the same for the rest of the months .


Please try this and see what happens.


Regards!
 
You must log in or register to reply here.
Back
Top