Steph,

Try this. Note the caveats in the first routines comments. It should save
you some time anyway. This won't work with protected sheets.

Robin Hammond
www.enhanceddatasystems.com

Sub TestWholeBook()
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
ColourCellsContainingNumericConstants wSheet.UsedRange
Next wSheet
End Sub

Sub ColourCellsContainingNumericConstants(rngTest As Range, _
Optional lColourConst As Long = 6579300, _
Optional lColourNumeric As Long = 13158600)
'---------------------------------------------------------------------------------------
' Procedure : ColourCellsContainingNumericConstants
' DateTime : 11/6/2004 09:58
' Author : Robin Hammond
' Purpose : colours cells containg hard coded constants in lColourConst
' colours cells containing constants as part of a formula in
lColourNumeric
' n.b. If a formula contains a valid constant this may give
misleading results
' e.g. =Left(A1,3) would pick up the 3 in the formula
'---------------------------------------------------------------------------------------
Dim rngCell As Range
Dim strMid As String
Dim strFormula As String
Dim nCounter As Integer
Dim nStart As Integer

On Error Resume Next
Set rngTest = Intersect(rngTest, rngTest.Parent.UsedRange)
On Error GoTo ColourCellsContainingNumericConstants_Error

If rngTest Is Nothing Then Exit Sub

For Each rngCell In rngTest

If Not IsEmpty(rngCell) Then

strFormula = rngCell.Formula
If IsNumeric(strFormula) Then

rngCell.Interior.Color = lColourConst

Else

If IsFormulaic(Left(strFormula, 1)) Then

nCounter = 1

Do While nCounter <= Len(strFormula)

'strRight = Mid(strFormula, nCounter)

Do While IsOperatorOrNull(Mid(strFormula, nCounter, 1))
And _
nCounter <= Len(strFormula)

nCounter = nCounter + 1

Loop

nStart = nCounter

Do While Not IsOperatorOrNull(Mid(strFormula, nCounter,
1)) And _
nCounter <= Len(strFormula)

nCounter = nCounter + 1

Loop

strMid = Mid(strFormula, nStart, nCounter - nStart)

If IsNumeric(strMid) Then

rngCell.Interior.Color = lColourNumeric
Exit Do

Else

nCounter = nCounter + 1

End If

Loop

End If
End If

End If

Next rngCell

On Error GoTo 0
Exit Sub

ColourCellsContainingNumericConstants_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
ColourCellsContainingNumericConstants of Module Module1"

End Sub

Public Function IsOperatorOrNull(strTest As String) As Boolean
'---------------------------------------------------------------------------------------
' Procedure : IsOperator
' DateTime : 4/16/2004 09:59
' Author : Robin Hammond
' Purpose : returns true if strTest is an operator character OR a space
'---------------------------------------------------------------------------------------
'
Dim strOps As String
On Error GoTo IsOperatorOrNull_Error

strOps = "+-*/,()=&^<:{}![] "
If InStr(strOps, strTest) 0 Then IsOperatorOrNull = True

On Error GoTo 0
Exit Function

IsOperatorOrNull_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
IsOperatorOrNull of Module mFunctions"
End Function

Public Function IsFormulaic(strTest As String) As Boolean
'---------------------------------------------------------------------------------------
' Procedure : IsFormulaic
' DateTime : 11/6/2004 09:50
' Author : Robin Hammond
' Purpose : returns true if strTest contains a possible formula starting
' with a +, - or = sign
'---------------------------------------------------------------------------------------
'
Dim strOps As String

On Error GoTo IsFormulaic_Error

strOps = "+-="
If InStr(strOps, strTest) 0 Then IsFormulaic = True

On Error GoTo 0
Exit Function

IsFormulaic_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
IsFormulaic of Module Module1"

End Function



"Steph" wrote in message
...
Hi everyone. I have a small piece of code that finds cells that have
hardcoded numbers in them and colors the cell blue. Unfortunately, it
only finds "true hardcoded" cells, for example, the number 23456. What it
does not find is =A1+23456. I have a huge file that my boss has
sporadically edited cells with numbers to make a certain value. I need to
find those
"edits" and remove them. Any ideas on how to modify the below code to
finds the numbers within a formula like =A1+23456? Thank you!


Sub ColorCellsOnce()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, 1).Font.ColorIndex = 5
On Error GoTo 0
End Sub