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

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

Hi, I am not sure I completely understand your question, do you want
to delete the edits or amend them or ...

a couple of ideas, one is once you have got all of the hardcoded
numbers turned blue you could write some code to ignore those and only
look at the ones that are left. ie:

Range("A1").Select
Do Until ActiveCell.Formula = ""

If ActiveCell.Font.ColorIndex < 5 Then
ActiveCell.ClearContents
End If

ActiveCell.Offset(1, 0).Select

Loop

This would then scroll down column a and delete any thing that wasn't
blue.

The other option is that you could use:

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

Which would then turn any formulas red, I have probable misunderstood
your question but hope my ideas help.

James


"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

Hi James. Basically, I just want to find the edits. I wasn't trying to
edit, ammend or delete them. Simply finding them would be a big help. I
have literally thousands of cells inside an Income Statement that are linked
to other worksheets and workbooks. In order to make the Income statement
look like my boss wanted it tp look like (numbers wise), he began simply
picking cells and ammending them with + or - hardcoded numbers. This was
last year. Now I want ti use the same model, but fear there are still the
hardcoded edits in the file, which don't apply this year! So really all I
want to do is FIND (color them somehow) the cells that are not true links.
Thanks for your help!
"James Butler" wrote in message
m...
Hi, I am not sure I completely understand your question, do you want
to delete the edits or amend them or ...

a couple of ideas, one is once you have got all of the hardcoded
numbers turned blue you could write some code to ignore those and only
look at the ones that are left. ie:

Range("A1").Select
Do Until ActiveCell.Formula = ""

If ActiveCell.Font.ColorIndex < 5 Then
ActiveCell.ClearContents
End If

ActiveCell.Offset(1, 0).Select

Loop

This would then scroll down column a and delete any thing that wasn't
blue.

The other option is that you could use:

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

Which would then turn any formulas red, I have probable misunderstood
your question but hope my ideas help.

James


"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