Forum Discussion
Solved
Formatting a table based on todays date
I created a table that contains information the next time employees are eligible to order uniforms based on their start date. I want the spreadsheet to highlight the row for each employee based on "today's" date. I add funds to their accounts, which allows the employee to order their uniforms when they are ready for new ones.
I created a table that includes conditional formatting. Now I use "Format only cells that contain". I am using TODAY() date as the criteria. I want the formula to select the entire row. Right now, it only selects the cell.
I would also like for the table to email or notify me daily who is eligible. Is this question more of a VBA than conditional formatting?
Select all data rows of the table. The active cell in the selection should be in the first data row.
In the following, I'll assume that the first data row is row 2, and that the relevant date column is column D.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$D2=TODAY()
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.To receive an email with eligible employees would require VBA.
4 Replies
Thank you that worked. Now I will work on a VBA script, so my file sends me an email on the date we have an employee eligible for a new set of uniforms.
I have columns A-T. The dates I am looking for will be in columns $F2-207 all the way to T2-207.
I created a simple formula that gives me a date 6 months from the previous date. The original date was the employee's start date. From the start date it goes every six months.
Do you want to highlight a row if any of the dates in columns F to T equals the current date? If so, use the steps in my previous reply, but with formula
=OR($F2:$T2=TODAY())
Select all data rows of the table. The active cell in the selection should be in the first data row.
In the following, I'll assume that the first data row is row 2, and that the relevant date column is column D.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$D2=TODAY()
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.To receive an email with eligible employees would require VBA.
