Colour a cell based on the colour of a range of other cells

Hi all,

I have created a spreadsheet to track the insurance and qualifications of our sub-contractors.

I have entered a table and in the first information column (column b) I have a drop down menu that you can select 'obtained' 'not obtained' or 'NA', these cells will highlight in green if obtained, red if not obtained and stay white/clear if NA or blank.

In the adjacent column (column c), I would enter the expiry date of those documents in column b and have added a formula that highlights the cell green if the expiry date is 1 month or more from todays date, highlight in amber if the date is equal to or within 1 month of todays date and highlight in red if the expiry date has passed.

I have another cell that I would like to highlight in green and say 'approved' if all cells in columns b and c are either white, amber or green; indicating that their documents are up-to-date. Equally, I would like the cell to highlight red stating 'not approved' if any of the cells in columns b and c are highlighted red, indicating that their documents have expired and need updated before they can be approved again. Is this possible?

I am happy to send over additional information if it is required.

Thank you in advance.

I have attempted to add an attachment of an example worksheet. I cannot see anywhere that it is attached once I close the 'manage attachment page'

Thank you so much.

I have inputted so many formulas over the last couple of days I couldn't even think straight haha.