Excel: use formula in cell format

microsoft excel

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.

Best Answer

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.

Related Question