Conditioning formatting based on multiple values in multiple cells.

[Canary99]

I am looking to first change the formatting and wording in a cell dependant on the value given in he first cell and the ratings giving in three other cells on another worksheet contained within the workbook.



Let me explain a little more.

I have a spreadsheet containing Metrics that have differing values of “Compliant”, “At risk “and “Not compliant” These values may be:

“Compliant” >99%, “At risk “<>90-99%, “Not compliant” <90% for one metric but have differing values on another metric: “Compliant” >80%, “At risk “<>70-79%, “Not compliant” <70%



I would like to change the value of a cell depending on the value of a designated cells entered % value ,as an example the Tier cell in the picture attached or any other cell, With the value and colour of the formatted cell changing to Red, Amber or Green and words “Compliant” “At risk” or “Not compliant” as required.

 

[Canary99]

Update and clarity with Sample uploaded
What I am looking for is when a % is input in Sheet "Input" Cell B2, B3 Etc a lookup on Cell B1is carried out against column "B" sheet lookup. Then the cell "C3" in Sheet "input" is updated to "Not Compliant", "At Risk" and "Comliant" with red, amber and green fill depending upon the acceptable values in sheet "lookup" colums D, E and F
View attachment 47299

 

[etaf]

not sure i follow exactly

when a % is input in Sheet "Input" Cell B2, B3 Etc a lookup on Cell B1is carried out against column "B" sheet lookup

Input Sheet B2 has say 80% , then look what up in B1 ?
and then again in column B

can you use XL2BB or upload to a share like onedrive,dropbox , googlespreadsheet
with examples

I suspect a CountIF() or an XLOOKUP() with TRUE / FALSE to use in conditional formatting
But no sure