I don't believe there's a formula for this, nor is there an option on the conditional formatting manager.
You can, however, use VBA. Here's a small function to get you started:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Do nothing if more than one cell is updated, or if cell contents are deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then
Select Case LCase(Target.Value)
newcolor = RGB(255, 0, 0)
newcolor = RGB(0, 0, 255)
newcolor = RGB(0, 255, 0)
newcolor = RGB(224, 176, 255)
newcolor = Target.Interior.Color
Target.Interior.Color = newcolor
Application.EnableEvents = True
- Press Alt + F11.
- On the Project Explorer (left panel), double-click the worksheet for which you'd like to apply the formatting.
- Paste the code in the window.
The subroutine will be automatically executed:
- When you type "red," "blue," "chartreuse" or "lavender" above in any cell in that particular worksheet
- When a formula in any cell returns either "red," "blue," "chartreuse" or "lavender"
You don't have to manually run the function whenever you need to color any cells.
You could also limit the cells on which this function will "operate" by changing the condition in the IF block. For example, to color cells in column C only, you can change the IF block above to the ff:
If Target.Cells.Count > 1 Or Target.Column <> 3 Then
Of course, you'd have to save your workbook as a macro-enabled document.