PDA

View Full Version : Find "Hard Coded" cells?



MrRhodes2004
01-26-2010, 12:27 PM
Group,

Recently, I was reviewing excel generated printed output and I found errors in the calculations. When I asked the engineer about the calculation errors, they returned to their spreadsheet and found that some cells that had been calculating before were somehow changed to a "hard coded" value, either =15 or =15+3 or just a value but not the original equation thus causing the errors.

I would like to try and develop a macro that would help the user Find "Hard Coded" cells and it would follow these steps:

1. Ask user to select range of cells to check
2. Check each cell in selected range to ensure that there is an equation, referencing at least one other cell. =15+3 is not an equation.
3. If cell is found, highlight cell.
4. Report in a msgbox, "X number of possible hard coded cells found."

If there is already a function out there, please send a link. Otherwise, I am not sure how to search the cells of the range to determine if they are calculating with the use of other cells.

Thanks for your help!

MrRhodes2004
01-26-2010, 03:55 PM
Okay, so this is what I came up with. It isn't perfect but it works.

Do you have a better way?

Sub CheckForHardCodes()
Dim Rng, InputRnge As Range
Dim Sh As Worksheet
Dim c As Range
Dim x As Long
Dim Response

Response = MsgBox("Hard coded cells will be highlighted yellow. Save file first.", vbOKCancel)
If Response = vbCancel Then
Exit Sub
End If

Set InputRnge = Application.InputBox(prompt:="Select Range of Cells to Check", Type:=8)

On Error GoTo NoPrededents

x = 1

For Each c In InputRnge
If IsEmpty(c) = False Then
Debug.Print c.Precedents.Address
End If
Next c
Exit Sub
NoPrededents:

With c.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Resume Next
End Sub