After many years of diving in and out of the Mr Excel forum for answers to many small questions; it's time to get in and start building up my understanding of macros piece by piece with a figuring some different scenarios these can apply to. Great place, although I've mainly used as reference for formulas and the odd macro for hiding rows and columns to customize various views.

I'm looking to put together a macro that will fill a cell along rows of a table based on certain criteria. I've used this to track actions from meetings however cell E3 had a lengthy formula and didn't cover everything I want to get achieve (along with being difficult to easily alter).

This is intended to run through the length of a table. Apologies if the below gets a little confusing. Dates are DD/MM/YY.

Cell A1 has date of previous meeting ie 01/05/18

Cell A3 has activity description

Cell B3 has date raised ie "04/05/2018"

Cell C3 has date due or note ie "10/05/18" or "Note"

Cell D3 has date complete ie "09/05/18"

Cell E3 has status (this is the cell I want to auto-fill)

1. If Cell B1 is not blank, but cell C3 is blank, cell E3 returns "No Due Date"

2. If Cell D3 is not blank (ie has a completed date) then cell E3 changes to "Complete", or "Closed" if cell D3 is prior to cell A1 - 7 days

3. If Cell C3 says "Note" then, E3 returns "Note" or "Old Note" if B3 is prior to cell A1

4. If Cell C3 date equals todays date then cell E3 returns "Due Today"

5. If Cell C3 date is within 7 days of todays date cell E3 Returns "Due Within 7 Days"

6. If Cell C3 dats is between 7-14 days of todays date cell E3 returns "Due Within 14 Days"

7. If Cell C3 date is greater than 14 days from todays date cell E3 returns "Open"

8. If Cell C3 date less than todays date then cell E3 returns "Overdue"

This would need to run to the end of the table - note sure how you would define that.

End goal would be to color each row in column C of the table based on the outcome ie "overdue" red, "due today" orange etc.

Previous Meeting: | 1/05/2018 | (Assume todays date is 18/05/18) | ||

Item | Raised | Due | Complete | Status |

Task 1 | 4/05/2018 | No Due Date | ||

Task 2.1 | 4/05/2018 | 7/05/2018 | 8/05/2018 | Complete |

Task 2.2 | 1/04/2018 | 8/04/2018 | 9/04/2018 | Closed |

Task 3 | 4/05/2018 | Note | Note | |

Task 4 | 4/05/2018 | 18/05/2018 | Due Today | |

Task 5 | 4/05/2018 | 20/05/2018 | Due Within 7 Days | |

Task 6 | 4/05/2018 | 28/05/2018 | Due Within 14 Days | |

Task 7 | 4/05/2018 | 2/06/2018 | Open | |

Task 8 | 4/05/2018 | 15/05/2018 | Overdue |

<tbody>

</tbody><colgroup><col><col span="3"><col></colgroup>