0

In my worksheet, the cells in column 4 contain a formula. Sometimes the formula returns a number, and sometimes it returns the string "data!". For the latter result, I want the font color to change to red. The following event handler code fails:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

'font color red if error
If Target.Column = 4 Then
    If Target = "data!" Then
        Range("D" & Target.Row).Font.ColorIndex = 3
    Else
        Range("D" & Target.Row).Font.ColorIndex = 1
    End If
End If

Application.EnableEvents = True
End Sub

Perhaps the code is seeing the formula instead of the value the formula returns, so I added the evaluate method to operate on Target:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

'font color red if error
If Target.Column = 4 Then
    If Evaluate(Target) = "data!" Then
        Range("D" & Target.Row).Font.ColorIndex = 3
    Else
        Range("D" & Target.Row).Font.ColorIndex = 1
    End If
End If

Application.EnableEvents = True
End Sub

This also does not work. No error, just no font color change. What is the problem?

Community
  • 1
  • 1
jmaz
  • 507
  • 3
  • 8
  • 19

1 Answers1

1

As far as I know, the target of the Worksheet_Change is a single cell (the one you click on and edit). When I modified your first code sample to use

If Target.Value = "data!" Then

it worked just fine. Whenever I would type "data!" in a cell in column 4 it would change the font color to red. Though the values of the cells do indeed change with a formula, the cells themselves aren't considered to be targets of a "Change" -event.

If you want to check the whole column and update the colors all at once - there are two options that I know of:

  • Conditional formatting

Here's some information about how to implement conditional formatting. There's also this question that had to do with creating conditional formatting with VBA.

  • A macro that you trigger with a button, a timer, on calculate - or some other event.

For example this small method could be called to color the cells.

Sub Main()

Dim r As Range

Set r = ThisWorkbook.Sheets(1).Range("D:D").SpecialCells(xlCellTypeFormulas)
r.Cells.Font.ColorIndex = 0
For Each Cell In r
    If Cell.Value = "data!" Then Cell.Font.ColorIndex = 3
Next

End Sub

EDIT: For calling the Main method whenever the worksheet calculates formulas, you could add

Private Sub Worksheet_Calculate()
Call Main
End Sub

to the code of the worksheet you want to use the functionality on. This will automatically update the colors of the range whenever formulas calculate, which should be the desired effect.

HTH

Community
  • 1
  • 1
natancodes
  • 998
  • 8
  • 12
  • +1 for a thorough answer. However, your vba solution does not work for me when I have a formula in the cell. It does work if I overwrite the formula with the "data!" string, but I need the formula. Tell me, does it work for you if your cell contains a formula that returns "data!"? I would be surprised if it did. – jmaz Jun 09 '14 at 20:08
  • With SpecialCells(xlCellTypeFormulas) it only works for cells that generate "data!" string from a formula. If I type "data!" directly to a cell and call the macro - the cell doesn't change the font color. You could switch the SpecialCells type or remove it completely if you want it to also affect constants. To clarify - I'm talking about the Sub Main() method here, the Workbook_Change won't be able to update the colors from formulas. – natancodes Jun 10 '14 at 05:53
  • I edited the answer to also contain a sample of how to call the Main method from the Worksheet_Calculate event. I think it should do the trick. You could just do the whole thing in the Worksheet_Calculate event too like Tim Williams suggested! – natancodes Jun 10 '14 at 06:09
  • Worked like a charm. Thank you for giving it another shot. I chose to go with a worksheet_Change event. – jmaz Jun 10 '14 at 16:16