Identify Cells types via VBA

[Sam Parker]

I am looking for a way to colour code cells to differentiate cells that :
Type 1. Contain a constant (inputs)
Type 2. Contain a 'straight' formula ie. DO NOT use an input cells of Type 1
Type 3. Contrain formulas that DO use input cells

Being a newbie, I'm not sure where to start.

But was wondering if there was a way programmatically through VBA to identify if a cells formula has any dependant cells that it uses (like when you click on the formula and excel highlights the dependant cells) and if there are any then that would mean they are of type 3 otherwise they would be of type 2. If it didnt contain an '=' sign then it would be of Type 1.

Any help with the above would be awesome ! ......thanks.

[richardreye]

Interesting request. I think this will do the trick. Change the range to suit the range you want highlighted in different colours. It uses the fact that Precedents.Select returns an error to distinguish Type 2 & 3. It may need some more error checking code added just in case other errors pop up but I'm still a little new to using errors to one's advantage like this.

Sub Test()

For Each cell In Range("A1:A4")

If Left(cell.Formula, 1) <> "=" Then
cell.Interior.ColorIndex = 14
Else
On Error Resume Next

cell.Precedents.Select

If Err.Number = 1004 Then
cell.Interior.ColorIndex = 15
Err.Clear
Else
cell.Interior.ColorIndex = 16
End If
End If

Next cell

End Sub

HTH