Adding CASE statement to flag percentage available less than 5% as red ELSE green
In this instance replacing td = CAST([PercentUsed]as varchar(30)), with something like:

td = '&lt;span style="color:' + CASE WHEN PercentUsed &gt; 95 THEN '#FF0000' ELSE '#40C211' + '"&gt;'
   + CAST([PercentUsed]as varchar(30))
   + '&lt;/span&gt;'
   ,


is probably what you are looking for. Or if you need to use font tags instead of more modern styling options (perhaps because some of your recipients have truly ancient email readers) then edit as needed.

Your inner query is using the FOR XML hack* to concatenate everything into a string, the column names forming tags (in this case they are all td) and the PATH parameter meaning each row gets wrapped in &lt;tr&gt;/&lt;/tr&gt;. For more information on forming XML document fragments (or in this case, HTML document fragments) this way see the official documentation such as https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server and for various tutorials on using this for string building more generally search for "TSQL FOR XML concatenation" or similar.

* I call it a hack as you are not actually returning an XML typed result, which is what the feature is designed for, it is being used here in a way that makes it behave like a string concatenation aggregate, the most common use for FOR XML in my experience, see articles like https://sqlblog.org/2011/03/08/t-sql-tuesday-16-this-is-not-the-aggregate-youre-looking-for for notes on this and other techniques to achieve the same result.