Question: how to create a formula in excel to change color based on text?
Answer: Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.
Click HOME > Conditional Formatting > Highlight Cells Rules > Text that Contains. ... 
Select the color format for the text, and click OK.