Color cell font text based on cell type? How do it.

[Chet]

I can write vba code but this has me stumped. Does anyone have an
idea how to format cell text color by the property of the type of cell
it is?

For example if the cell is a constant the font would be red, if the
cell has a formula in it then it would be blue, and if the cell is
linked to another part of the workbook then it would be green.

I would be greatful for any ideas.

Thanks,

Chet

 

[JLGWhiz]

Sub ColorSpecialCellsFont()
'The If statement avoids error message
Set myRange = Sheets(1).Range("b2:b6")
If Not myRange.SpecialCells(xlCellTypeFormulas).Count < 1 Then
myRange.SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
End If
End Sub

 

[Chet]

Sub ColorSpecialCellsFont()
   'The If statement avoids error message
   Set myRange = Sheets(1).Range("b2:b6")
   If Not myRange.SpecialCells(xlCellTypeFormulas).Count < 1 Then
     myRange.SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
   End If
End Sub







- Show quoted text -

I kind of understand you are doing this, but how would i detect cells
where that particular cell is linked to another cell?

 

[JLGWhiz]

In that case you look for precedents.

ActiveCell.ShowPrecedents

 

[Rick Rothstein]

Can you clarify something for me? What exactly do you mean when you say "if
the cell is linked to another part of the workbook"?

--
Rick (MVP - Excel)

Sub ColorSpecialCellsFont()
'The If statement avoids error message
Set myRange = Sheets(1).Range("b2:b6")
If Not myRange.SpecialCells(xlCellTypeFormulas).Count < 1 Then
myRange.SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
End If
End Sub







- Show quoted text -

I kind of understand you are doing this, but how would i detect cells
where that particular cell is linked to another cell?

 

[Chet]

Can you clarify something for me? What exactly do you mean when you say "if
the cell is linked to another part of the workbook"?

--
Rick (MVP - Excel)






I kind of understand you are doing this, but how would i detect cells
where that particular cell is linked to another cell?- Hide quoted text -

- Show quoted text -

I wasn't very clear and I gave it some more thought. I'm trying to
show cells in three different ways.

1. A constant just entered as a number
2. A direct link where a cell has contents something like =A5 or
=Sheet2!B8
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)

Thanks,
Chet