Hi Hayley,
'colour fill' is 'format', not 'content'
Formulas can be used to set the content of the cell containing the formula.
'Format' can be changed by the user, or, in the case of colour fill or text colour or style, by Conditional Highlighting rules.
Conditional Highlighting rules compare the content of a cell with another value. The 'other value' may be written into the rule, or may be contained in a separate cell.
Wayne's formula in the post above will place the text "ORANGE CELL = CLIENT TO PROVIDE MISSING INFORMATION" in cell G10 if at least one of cells B12, C12, B13, and C13 is empty, and leave cell G10 'empty'* if all four of those cells contain text, numbers, or any other value.
In the example shown in your post, all four of the cells contain text: "Jersey Cotton" and "157cm" in row 12, and "TBA" and "TBA" in row 13. COUNTA will count all four of those cells and return 4, and the formula will place a null string ( "" ) in G10, making that cell appear 'empty'
Suggestions:
If you want "TBA" to always mean "additional information required from client', add this Conditional Highlighting rule to every cell that could contain TBA with this meaning:
Text is "TBA"
Orange Fill
This will give these cells an orange fill if the cell contains TBA, and Wayne's formula, revised to count only cells containing "TBA" will insert the 'orange print message if the count of 'TBA' cells is greater than zero.
Adding the revised formula, shown at the bottom of the screen shot below, to cell G10 shows the 'orange' message when there are cells in the selected area containing TBA.
And removes the message when there are no cells i the selected range containing TBA:
Text version of formula that can be copied, then pasted into the formula editor.
IF(COUNTIF(B12:D13,"TBA")>0,"ORANGE CELL = CLIENT TO PROVIDE MISSING INFORMATION", "")
Regards,
Barry