VBA Format Cell Font based on Formula contents

[OVH]

I'm new to VBA and I'm trying to select a range and format all of the cells in the range differently based on the cell contents. Basically if the cell is a link to another workbook then change font to red, if the cell is a link to another sheet (same workbook) then format as green, if a formula in the same sheet then font should be blue, if it's a manual input format as black.

If cell.formula contains a "]" and a "!", then
cell.font.color=255
else
If Cell.Formula contains a "!" but not a "]", then
cell.font.color=34310
else
If Cell.Formula contains a "=" but not a "]" or a "!", then
cell.font.color=16711680
Else
Cell.font.color=0

 

[rconverse]

I think you could accomplish this with just conditional formatting. Looking at your first if scenario, this formula would work:

=ISNUMBER(SEARCH("]", A1)+SEARCH("!", A1))

You could just apply the same type logic in your other formulas and then apply those rules to your range.

HTH,
Roger

 

[Snakehips]

OVH,

VBA-wise..... Perhaps try this to format the selected range based on your stated criteria.

Sub FMat()
For Each Cell In Selection
If Cell.Formula Like "*]*" And Cell.Formula Like "*!*" Then
Cell.Font.Color = 255
ElseIf Not Cell.Formula Like "*]*" And Cell.Formula Like "*!*" Then
Cell.Font.Color = 34310
ElseIf Not Cell.Formula Like "*]*" And Not Cell.Formula Like "*!*" And Cell.Formula Like "=*" Then
Cell.Font.Color = 16711680
Else
Cell.Font.Color = 0
End If
Next Cell
End Sub

Hope that helps.