Delete all

OK.

I think that the attached version is getting close to what you're looking for but I need to clarify this statement:

"the sheet remains but it should also delete the data in the no colour cells. e.g. for each sheet linked to a row, cell content for cell, C4,E4,G4,C6,E6,G6,C8,E8,G8, merged cells (C-G) in rows 10,12,14,16,18,20,22 and 24"

In the sample sheets you supplied, there were only formulae in cells C4,E4,G4,C6,E8,G8. You seem to indicate that there are going to be formulas in other cells.
To accelerate insertion of formulas, I've added a button at bottom left of the FRONT sheet which uses a macro to automatically add the relevant formulas to each of the 18 sheets added.
You can test this button's functionality by deleting the sample formulas you show on the added sheets and then clicking the button to reinsert them.
However, at present, the macro only adds links to the 6 cells your sample supplied. If there are additional cells with formula, please upload a version with 1 sample sheet showing ALL formulas.

Please note that when you click the 'Delete all' button on the FRONT sheet, the linked sheets will display blanks for any cells linking back to row of the FRONT sheet from which data was deleted.
This seems to satisfy your requirement to delete the data in the no colour cells. Let me know if I'm misunderstanding.

OK.

My head is spinning a bit after this so I hope this version has you sorted!

I added a code which allows you to show or hide the Add formulas to sheets button- to show the button, type "show" in cell C1 of the FRONT sheet (no inverted commas, case sensitive). To hide it, type "hide" (same rules).

The 'Delete all button is generally visible at the extreme right of the FRONT worksheet, occasionally a little cut off but I ended up not grasping the nature of how it has to be positioned.
Clicking it deletes any free text from the relevant sheet as well as the specified content on the FRONT sheet.