The title is intriguing isn’t it? Actually I am going to show you how to track when there has been no activity in certain cells in your Worksheet!
Suppose you have your own business and you have several hundred clients and your business is still growing. Now you want to make sure that you make that phone call to each of your clients to remind them about your business or send and e-mail for the same purpose.
Surely you do not want to have to scroll through all those clients to see when you last did something for them!
If you have a worksheet for your client list something like this (column A contains client name, B the date, C start time, D end time) there are a couple of ways to distinguish if you have not done something for that client that you had wanted to do. It all depends on the data characteristics that you are dealing with. If you have a single line in your data table for each of your clients, you can use Conditional Formatting and create a rule that will highlight clients or change the color of the cell with dates in your data, noting when you last sent that e-mail or made that phone call, and the date is older than 90 days.
Follow the steps below to learn how:
- Select the cells or range where you would like this rule to apply.
- On the Home tab of your Ribbon, in the Styles group, click the arrow next to Conditional Formatting to display the dialog box.
- Select New Rule from the dialog box.
- In the Select a Rule Type, choose Use a Formula to determine which cells to format.
- In the Edit the Rules Description area, key in =(TODAY()-B2)>=90.
- Then click Format to display the Formatting dialog box.
- Click the Fill tab.
- Under Background Color, select the color of your choice (a light color is usually a good choice).
- Click OK.
- Click OK once more.
Depending upon your choices, your cell(s) should now look like the ones below.
A special thank you to Allen Wyatt from whom I learned that formula!