Compare formula/ xlInconsistentFormula

[ra]

Hi All, I want to look accross a row of formulas and highlight cell
where the formula has changed or is hardcoded.

I have been trying to use xlInconsistentFormula but not having much
luck.. any help or suggestions welcomed.

thanks
Ra

 

[Guest]

You could try something like this:

Sub Inconsistent()
Dim myRange As Range, r As Range
Dim StrFormula As String


Set myRange = Range("B8:I8")
StrFormula = Cells(myRange.Row, myRange.Column).FormulaR1C1
For Each r In myRange
If r.FormulaR1C1 <> StrFormula Then
r.Interior.ColorIndex = 6
Debug.Print r.FormulaR1C1
End If
Next r

End Sub

HTH,
Barb Reinhardt

 

[ra]

Thanks, the below code works. -One improvement I need to work on
is to treat each row individually within selection, so it highlights
unique formulas per row rather than per sheet...

Sub Audit_Tool_1()


'Highlights Unique formula's within total selection
Dim rngCell As range, rng As range
Dim strTest As String
Set rng = Application.InputBox(prompt:="Select Range to be
evaluated",
Type:=8)


For Each rngCell In rng
If InStr(1, strTest, rngCell.FormulaR1C1, vbBinaryCompare) = 0
And
_
Len(rngCell.Text) > 0 Then
strTest = strTest & "|" & rngCell.FormulaR1C1
rngCell.Interior.ColorIndex = 27
Else
rngCell.Interior.ColorIndex = xlNone
End If
Next
'Highlight Constants (hardcoded) Cells *Note: does not include
constants that contain = (equals)
' E.g. will pick up entry of "30,000" but not "=30,000"
On Error GoTo NotFound
rng.SpecialCells(xlCellTypeConstants, 21).Select
Selection.Interior.ColorIndex = 40
Selection.Font.ColorIndex = 0


' Exit Sub
NotFound:
MsgBox "Finished"


End Sub