Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 11 '11 at 12:44

This question came from our site for professional and enthusiast programmers.

1  
So what you're asking is that while you know you can code this in VBA, you'd rather not? If so, this question should probably be on Superuser.com. –  Phil.Wheeler Sep 11 '11 at 9:52
    
But how this VBA should be triggered? Upon every change in the worksheet? For a big worksheet this will hinder performance significantly. –  Assaf286294 Sep 11 '11 at 10:25
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

enter image description here

enter image description here

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.

share|improve this answer
    
I guess that for small and simple cases that will do, but for a big and complex worksheet it may slow things down. What if the color formula references a different worksheet and not just the cell itself? –  Assaf286294 Sep 11 '11 at 10:31
    
@user any changes in any of the worksheets will trigger the event. Yes, it may slow it down a bit. You could run it every few changes by hand. –  jonsca Sep 11 '11 at 10:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.