Hello,
I got a Excel calendar somewhere from Internet, with Conditional Formula that highlighted when a date match with some event dates.
Working code is:
I am trying to recreate it but without table, just using range, but it's not working.
Please Login or Register to view this content.
here's current code:
Please Login or Register to view this content.
Attached is example of the sheet.
contain text A = Red, contain text B = Yellow , contain text C
Appreciate for any help. Thank you
Last edited by qiyusi; 03-22-2017 at 09:38 AM.
Right Click on your Excel Table, from menu select Table -> Convert to Range
Hello,
Thank you for your respond.
How ever, that's not what i need, it's already in Range format, so no need to convert.
The problem is the formula not working at all.
Did you manually type in "$J$18:$J$21"? I only ask because if your sheet were converted with a table and you selected the range, it would not show up as "$J$18:$J$21".
If you did manually type in "$J$18:$J$21", try deleting it and select the range instead.
EDIT: I downloaded this sheet and I dont think this is setup correctly. Do you have the original that you downloaded where you said it worked? If so, please upload that as well.
Last edited by McStagger; 03-22-2017 at 11:09 AM.
I didn't say: do this on your own table
I said: do this on excel table on original file and probably you will see formulas converted to normal, but without original file I can say nothing more.
Hi Sandy and McStagger,
Thank you for both respond,
Yes, attached is the working formula that i got from the net.
for A,B,C text formatting,
i have tried : OR(ISNUMBER(SEARCH("TEXT",referencecell),referencecell) is working good.
you can try =IF(B6="",FALSE,SUMPRODUCT((B6>=$AA$5:$AA$10)*(B6<=$AB$5:$AB$10)))
this is "table" formula converted to normal
second formula is normal and it works
if you want more events change red accordingly to your needs
Last edited by sandy666; 03-22-2017 at 06:59 PM.
Hi,
Thank you very much, this work great! Yes the formula now works.
another thing is, there is one condition, where the event only one day, so the J column is blank.
this causing the conditional formula not working properly.
trying to nested if with IF J4 blank, then B3 = I4.
Please Login or Register to view this content.
With last example file:
Conditional Formatting doesn't work like you think.
Basic description:
1. Must be defined top-left cell of the range (here B6)
2. CF works for TRUE or FALSE (1 or 0) - if TRUE show color if FALSE do nothing
and it works for one day, I defined 08/02/2013 (start event = end event) and one cell is colored.
btw. Months of start event and end event should be the same.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks