Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting - example (Excel 97 +)

    If I've done this right, the attached workbook ought to be useful for keeping track of birthdays, inventory re-order schedules, invoicing schedules etc.

    The first column contains item identifiers (in this case names of humans) with conditional formatting.
    The second column contains dates (in this case anniversaries). This column is populated with a RAND() function to simulate birthdays. Tap the F9 key to generate a new set of dates. In practice the dates would be hard-coded in the second column.

    The conditional formatting is meant to shade the cell green two weeks prior to the date, red two weeks after the date.
    You can read the values "14" in the conditional formula as "two weeks".

    My theory is that when I load the workbook, I'll get ample reminder of which people ought to receiving cards, and if I have neglected to mail a card, a red-faced reminder to send an apology.

    The conditional formulae can be inspected with this little macro<pre>Sub test()
    Debug.Print ActiveCell.FormatConditions(1).Formula1
    Debug.Print ActiveCell.FormatConditions(2).Formula1
    End Sub</pre>

    but if you can't/won't do that, here are my results after running the macro<pre>=AND(((DATE(YEAR(NOW()),MONTH(B2),DAY(B2 )))<NOW()),((DATE(YEAR(NOW()),MONTH(B2),DAY(B2)))> (NOW()-14)))
    =AND(((DATE(YEAR(NOW()),MONTH(B2),DAY(B2)))>=NOW() ),((DATE(YEAR(NOW()),MONTH(B2),DAY(B2)))<(NOW()+14 )))
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting - example (Excel 97 +)

    Thanks. Should be useful in itself, and also as an example of using conditional formatting.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting - example (Excel 97 +)

    > Should be useful in itself,
    I was thinking of you as I wrote it. I figured you could use something similar to remind yourself to check for new posts on the forums (grin!)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting - example (Excel 97 +)

    Ahem <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Conditional Formatting - example (Excel 97 +)

    Chris,

    This is very useful. However I notice that in the Rand () function that the birthdays dates generated are all in 1900's, how could the RAND () function be modified to be within a certain year, ie 2007?

    Any thoughts

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting - example (Excel 97 +)

    For example

    =DATE(2007,1,1) + INT(365*RAND())

    This adds a random number of days in the range 0 ... 364 to the 1st of January.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Conditional Formatting - example (Excel 97 +)

    Thanks Hans

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting - example (Excel 97 +)

    > within a certain year, ie 2007?
    I note Hans's prompt reply. (Thinks: He must already be using my spreadsheet ...").
    I wrote the random dates solely to watch the effect on my formatting. I think you already know that. The randomness doesn't form any part of the conditional formatting - it was placed because I am too lazy to generate random dates. Also I like seeing the lights flash. I thought afterwards that the red-and-green flashing might make a suitable Christmas decoration ...

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Conditional Formatting - example (Excel 97 +)

    Chris, thanks for the repy - I am equally impressed with the flashing lights; Am aware that the random dates don't form part of the conditional formatting - but wanted to gererate dates in 2007 so I could make my lights flash <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting - example (Excel 97 +)

    > make my lights flash
    I trust you are now suitably "lit up"!

Posting Permissions

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