Changing font color based on type cell it is. How to do it?

[Chet]

I want to change the font color on individual cells in a range based
on three different criteria.

1. A constant just entered as a number. (red font color)
2. A direct link where a cell has contents something like =A5 or
=Sheet2!B8 (green font color)
3. Any other type of formula such as =vlookup(a4,b4:b9,2,0). I
realize that this item is probably a subset of #2 but I am trying to
change the font color on something like this and make it different
than #2. (and different than #1) (blue font color) So direct link
cells are excluded from this group.

Thanks,
Chet

 

[Barb Reinhardt]

This will get you red font if it has no formula.

If Not Selection.HasFormula Then
Selection.Font.ColorIndex = 3
End If

It's tough to know what to suggest for the other. Maybe search for a
Parenthesis and format based on it's existance.

 

[Mike]

Try this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each Target In rng
If Target.HasFormula Then
If Left(Target.Formula, 8) = "=VLOOKUP" Then
Target.Font.Color = -10477568
Else
Target.Font.Color = -11489280
End If
Else
Target.Font.Color = -16777024
End If
Next Target
End Sub

 

[Mike]

How about something like this
Left(Target.Formula, 8) = "=VLOOKUP"

 

[Chet]

Try this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each Target In rng
    If Target.HasFormula Then
        If Left(Target.Formula, 8) = "=VLOOKUP" Then
           Target.Font.Color = -10477568
        Else
           Target.Font.Color = -11489280
        End If
    Else
        Target.Font.Color = -16777024
    End If
Next Target
End Sub







- Show quoted text -

The only problem I see here is that the formula is not necessarily a
vlookup. It could be any formula. Chet

 

[JLGWhiz]

After reviewing the different types of links, formulas, names, controls, etc.
and the different types of formulas that create precedents and dependents as
links, it looks to me like you are chasing a phantom. There does not seem to
be a simple way to separate the type of link. I can color a cell's fonts if
it has a formula in it. I can color a cell's fonts if it has precedents. I
can color a cell's fonts if it has dependents. But trying to distinguish the
types of formulas that create the precedents or dependents is beyond any code
I have ever seen in VBA.

 

[Chet]

After reviewing the different types of links, formulas, names, controls, etc.
and the different types of formulas that create precedents and dependentsas
links, it looks to me like you are chasing a phantom.  There does not seem to
be a simple way to separate the type of link.  I can color a cell's fonts if
it has a formula in it.  I can color a cell's fonts if it has precedents.  I
can color a cell's fonts if it has dependents.  But trying to distinguish the
types of formulas that create the precedents or dependents is beyond any code
I have ever seen in VBA.






- Show quoted text -

OK.. well thanks for looking.. i do appreciate it.. Chet