Copy certain data from sheet 1 into sheet 2 if conditions are met.

[excelvolution]

Hello there - Excel notive when it comes to VBA. I've seen similar posts elsewhere, but really not sure what I'm looking at to copy/paste into my own.

> I have two tabs - Tab 1 (workstreams) contains RAG status of various projects - these get updated each week. Tab 2 (countermeasure) contains countermeasures for projects whose status is Red or Amber.
> I also locked the entire sheet with the exception of cells which need an update (highlighted in yellow)
> The RAG status is indicated by data validation - 1 (G), 2 (A), 3(R), 4(complete)

ASK:

1. When RAG status of a project initiative (columns I thru BH) changes to Red or Amber, I need to copy that project name (column A), project initiative number (column B), project initiative (column D), and project owner (column H) into tab 2 as a new line item starting on C8. Project name into column C, project initiative number into column D, project initiative into column D and project owner into column F.
2. If RAG status of a project initiative continues to be Red or Amber more than 1 consecutive week, I do not need a new line item created in tab 2.
3. If RAG status of a project initiative goes R/A one week, back to Green next week and back to R/A the third week, then I need 2 new line items created in tab 2 - one for each new time the project initiative went back to R/A (assuming there was a gap of Green between these two R/A status).

Can someone help me figure out how to do this? Essentially, I want the line item copied automatically because sometimes people forget to add countermeasure when the item changes to R/A, and I want to force that to happen by copying the line items. A BONUS would it if the "date raised" (column G in tab 2) can be automatic based on when the status was updated to R/A.

Thanks so much!