Excel: use formula in cell format

Question

Is there a way to use a formula as a cell format?

AFAIK, conditional formatting allows only static format applied based on some condition. I want the format itself to be dynamic. For example, I want to set the color of a cell to be its content (if the cell value is "red" then use red as the color), without setting a conditional formatting rule per each color.

I know that this can be done with a macro, but I don't want to run a macro after each cell change.

Answer 1

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
    Exit Sub
End If

Select Case LCase(Target.Value)
    Case "red"
        newcolor = RGB(255, 0, 0)
    Case "blue"
        newcolor = RGB(0, 0, 255)
    Case "chartreuse"
        newcolor = RGB(0, 255, 0)
    Case "lavender"
        newcolor = RGB(224, 176, 255)
    Case Else
        newcolor = Target.Interior.Color
End Select

Target.Interior.Color = newcolor
Application.EnableEvents = True

End Sub

To use:

1. Press Alt + F11.
2. On the Project Explorer (left panel), double-click the worksheet for which you'd like to apply the formatting.
3. 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
    Exit Sub
End If

Of course, you'd have to save your workbook as a macro-enabled document.

Answer 2

as far as I know, short of adding a conditional format for each color you can't do it without a macro. on the bright side, it shouldn't take long to make one for each color your using, I'd say a 5 minutes process.

Answer 3

I'm in agreement with the other poster in that it's likely you'll have to use a macro. You wouldn't have to run the macro by hand each time, as you could assign it to a SheetChange event.

Click on the Microsoft Excel Objects folder of your VBA Project in the VBE, then double click on ThisWorkbook to generate a code stub. Select Worksheet on the left hand side, and then select SheetChange on the right hand side.

It so happens that the example that I linked uses this event to control the color of the cell (you'd have to update the code to use the string "red","green","blue"instead of the case statement with the numerical values.