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

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Chet" wrote:

> 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
>

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
"Chet" wrote:

> 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
>

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

"Barb Reinhardt" wrote:

> 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.
> --
> HTH,
> Barb Reinhardt
>
> If this post was helpful to you, please click YES below.
>
>
>
> "Chet" wrote:
>
> > 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
> >

On Nov 23, 2:40*pm, Mike <M...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "Chet" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

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

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" wrote:

> On Nov 23, 2:40 pm, Mike <M...@discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "Chet" wrote:
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
> The only problem I see here is that the formula is not necessarily a
> vlookup. It could be any formula. Chet
>

On Nov 23, 5:37*pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 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.
>
>
>
> "Chet" wrote:
> > On Nov 23, 2:40 pm, Mike <M...@discussions.microsoft.com> wrote:
> > > 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
>
> > > "Chet" wrote:
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > The only problem I see here is that the formula is not necessarily a
> > vlookup. *It could be any formula. * Chet- Hide quoted text -
>
> - Show quoted text -

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