Coloring Cells by Date of Birth

1. 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. Re: Coloring Cells by Date of Birth

Originally Posted by 63falcondude
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.

3. 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. Re: Coloring Cells by Date of Birth

It worked great! Thank you again, this forum is such a valuable resource!

5. Re: Coloring Cells by Date of Birth

My pleasure. Happy to help.

6. 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. 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.

8. 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. 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.

10. Re: Coloring Cells by Date of Birth

Formula:
`Please Login or Register  to view this content.`

11. Re: Coloring Cells by Date of Birth

Originally Posted by 63falcondude
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. 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.

13. Re: Coloring Cells by Date of Birth

Originally Posted by 63falcondude
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. Re: Coloring Cells by Date of Birth

You're welcome.

Page 2 of 2 First 1 2

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

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