+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 29 of 29

Coloring Cells by Date of Birth

  1. #16
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    To check, I'll open the workbook that you shared in post #13, click on any cell that has CF applied to it, then go to Conditional Formatting > Manage Rules.

    It looks like there are 4 rules applied here. I'm going to delete that last one.

    Now, hovering over the first one shows that 18 is not included (we want >=18 not >18) so I'll delete that one too.

    Now, select the only red rule left and click on Edit Rule. Change both instances of $B1048397 to $B5 then select OK > OK and you should be good to go.

  2. #17
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    12

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    To check, I'll open the workbook that you shared in post #13, click on any cell that has CF applied to it, then go to Conditional Formatting > Manage Rules.

    It looks like there are 4 rules applied here. I'm going to delete that last one.

    Now, hovering over the first one shows that 18 is not included (we want >=18 not >18) so I'll delete that one too.

    Now, select the only red rule left and click on Edit Rule. Change both instances of $B1048397 to $B5 then select OK > OK and you should be good to go.

    I was curious if you could potentially help me with another issue. When I changed one of the rules for my worksheet a lot of the cells were highlighted that should not have been highlighted. See attached for my example.

    This problem starts at Row 51. Thanks again in advance.
    Attached Files Attached Files

  3. #18
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    You have a lot of unneeded CF there.

    Select all of the cells by clicking on the triangle above row 1 and to the left of column A > Conditional Formatting > Manage Rules > Delete all rules except for the first yellow and red rules.

    Now you should be left with two rules.

    Change the "Applies to" range to =$B5:$H1000 then select OK

    If you don't want the whole row (from B to H) highlighted, you can use this instead in the "Applies to" range: =$B5:$B1000,$H5:$H1000

  4. #19
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    12

    Re: Coloring Cells by Date of Birth

    It worked great! Thank you again, this forum is such a valuable resource!
    Last edited by jneiderman82; 06-11-2019 at 01:41 PM.

  5. #20
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    My pleasure. Happy to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #21
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    12

    Re: Coloring Cells by Date of Birth

    Is there a way that will update the age background color based on today's date and lets say a future date? If I was to alter the cell that has the date in it based on the formula, the updated cells don't get highlighted red, if they are over 18. I thought they would but maybe I'm missing something.

  7. #22
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    As it sits, the conditional formatting rules calculate the age based on the current date and the date of birth in column B.

    =AND(DATEDIF($B5,TODAY(),"y")>=18,ISNUMBER($B5))

    =AND(DATEDIF($B5,TODAY(),"y")<14,ISNUMBER($B5))

    For example, if you were to change the DOB in B5 to 12/11/2000, that person will now be 18 and so that row will now turn red.

    See attachment.
    Attached Files Attached Files
    Last edited by 63falcondude; 06-11-2019 at 02:12 PM.

  8. #23
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    12

    Re: Coloring Cells by Date of Birth

    In that same sheet, what if we were to change the date of F2 to a later date, why is that not changing the color in column H?

  9. #24
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    As mentioned in post #22, the Conditional Formatting rules calculate the current age. Not the age as of the date in F2.

    If you want the ages to be based on the date in F2, you can swap TODAY() in the CF formulas with $F$2.

    Edit: I now see that the formulas in column H were also based on F2 and not today's date.
    Last edited by 63falcondude; 06-11-2019 at 02:28 PM.

  10. #25
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    4,291

    Re: Coloring Cells by Date of Birth

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #26
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    12

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    As mentioned in post #22, the Conditional Formatting rules calculate the current age. Not the age as of the date in F2.

    If you want the ages to be based on the date in F2, you can swap TODAY() in the CF formulas with $F$2.

    Edit: I now see that the formulas in column H were also based on F2 and not today's date.
    Swapping the CF Formula with $F$2 took the fill color out. I'm sorry in advance for all the questions. Just trying to finalize this issue.

  12. #27
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    Select cell B5 > Conditional Formatting > Manage Rules > select the red rule > Edit Rule > Change formula from:

    =AND(DATEDIF($B5,TODAY(),"y")<16,ISNUMBER($B5))

    to

    =AND(DATEDIF($B5,$F$2,"y")<16,ISNUMBER($B5))

    Do the same for the yellow rule.

    Attached is the result from doing this.
    Attached Files Attached Files

  13. #28
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    12

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    Select cell B5 > Conditional Formatting > Manage Rules > select the red rule > Edit Rule > Change formula from:

    =AND(DATEDIF($B5,TODAY(),"y")<16,ISNUMBER($B5))

    to

    =AND(DATEDIF($B5,$F$2,"y")<16,ISNUMBER($B5))

    Do the same for the yellow rule.

    Attached is the result from doing this.
    Worked like a charm! Thank you again for all your help!!!

  14. #29
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Coloring Cells by Date of Birth

    You're welcome.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

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