+ Reply to Thread
Results 1 to 20 of 20

Conditional Formatting

  1. #1
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Conditional Formatting

    Hi,

    Using Excel 2003 and would like to add some formatting features.

    The sheet in question list customer records including renewal dates. Because these dates vary from customer to customer I’m finding it difficult to keep track for sending out reminders of renewals. I would like to make the renewal dates on the sheet sensitive to the current date so that I get a prompt (by change of colour) about 10 days before renewal date. Is this possible I have been looking at Conditional Formatting without success.
    Last edited by pcspike; 12-15-2011 at 09:22 AM.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formating help

    Hi pcspike.

    Assuming that you have a date in A1, then Conditional Formating>>>Formula is>>>TODAY()+10>A1>>CHOOSE YOUR COLOUR>>ok.

    Hope to helps you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formating help

    Hi Fotis,

    Thanks for your reply.

    Do I enter todays date between the brackets? If so, is there a particular format in which it should be entered. Also, what's the best way to test?

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formating help

    Hi again.

    For me is difficult to explain you better.

    So pls, take a look to the attachement.

    Hope to helps you.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formating help

    Today() is today, so no need for anything. Check the function's syntax with excel help

  6. #6
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formating help

    Hi,
    Many thanks for that, it's appreciated. Would it now be possible to replicate this to the whole column using something like copy/drag/special paste?

  7. #7
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formating help

    Replicate what to which column?

  8. #8
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formating help

    Quote Originally Posted by arthurbr View Post
    Replicate what to which column?
    As explained original question have list (column) of dates. I wish to apply TODAY()+10>A1 to the whole column of dates
    Last edited by pcspike; 12-14-2011 at 01:00 PM.

  9. #9
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formatting

    Amend the CF formula to =today()+10>$a1 then copy -Select the range you want formatted- Paste special - Formats

  10. #10
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formatting

    Quote Originally Posted by arthurbr View Post
    Amend the CF formula to =today()+10>$a1 then copy -Select the range you want formatted- Paste special - Formats
    That works great, thank you for the help.
    I noticed too in the Conditional Format that there is the option to 'Add' to the existing format (Condition 2 and 3). Does this mean that the format can have further options? If so, I was thinking obout after the 10 day notice that the the cell could be changed to a differnt colour 10 days after the event date.

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formatting

    You can indeed add more conditions, keeping in mind that they will be applied sequentially. So the logic should be checked carefully

  12. #12
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formatting

    Quote Originally Posted by arthurbr View Post
    You can indeed add more conditions, keeping in mind that they will be applied sequentially. So the logic should be checked carefully
    As per your suggestion for 10 day prior: =TODAY()+10>$D17. I have also added: =TODAY()-10>$D17 for 10 days after the event. I have used different colours red and green and find that they both work, but only when I use them separately (in different cells). When I try both; as condition 1 and 2, only condition 1 works. Would appreciate advice to get me over the last hurdle.

  13. #13
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formatting

    If you want to CF the period then days prior to the renewal date and the period 10 days after use
    Cond 1 =and(today()+10>$D17,today()<$D17)
    Cond 2 =and(today()-10<$D17,today()>$D17)

  14. #14
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting

    Ηι
    Τoday() is today, so no need for anything. Check the function's syntax with excel help
    arthurbr,this observation was for me?

    If, YES, then please tell me a bit because I did not understand .. what you mean.

  15. #15
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formatting

    It was an answer to post #3. Nothing to do with your post ( should have quoted, sorry)

  16. #16
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Smile Re: Conditional Formatting

    ...Never mind

    Best regards

  17. #17
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formatting

    Quote Originally Posted by arthurbr View Post
    If you want to CF the period then days prior to the renewal date and the period 10 days after use
    Cond 1 =and(today()+10>$D17,today()<$D17)
    Cond 2 =and(today()-10<$D17,today()>$D17)
    Everything set up and working just as I wanted, thanks again for your assistance.

  18. #18
    Registered User
    Join Date
    12-14-2011
    Location
    Lancs UK
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Conditional Formating help

    Quote Originally Posted by Fotis1991 View Post
    Hi again.

    For me is difficult to explain you better.

    So pls, take a look to the attachement.

    Hope to helps you.
    Thanks for your help with attachment Fotis

  19. #19
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting

    You are welcome!!

  20. #20
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formatting

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1