|If you wanted to compare all of the cells in the 2 worksheets, you should have asked for that. Since we can't read minds, all we can do is answer the question that is asked, which was to compare a single cell.|
Comparing all of the formulas in a worksheet is a bit more difficult and doesn't lend itself to using a UDF. I suggest a macro can be run against Sheets 1 & 2, comparing the sheets cell by cell over the UsedRange and checking the formulas.
Since I assume that some cells will contain formulas and others will not, the code only returns Fault or Same if at least one of the 2 cells it is comparing contains a formula. For example:
- If Sheet1!B3 and Sheet2!B3 both contain constants or text or anything else that is not a formula, the code will return "No Formula".
- If both cells contain a matching formula the code will return "Same".
- If both cells contain formulas but they don't match, or if only one of the 2 cells contains a formula, then the code will return "Fault".
The code also assumes that that the UsedRange in Sheet1 is at least as big as the UsedRange in Sheet2 since it uses the UsedRange in Sheet1 to determine how many cells to compare.
Note: The first line of this code will clear the contents of Sheet3 so that you get fresh data each time you run it. Make sure that you don't have anything in Sheet3 that you want to keep.
As always, I suggest that you test this code in a backup copy of your workbook since macros can not easily be undone.
For Each myCell In Sheets(1).UsedRange
If Sheets(1).Range(myCell.Address).HasFormula Or _
If Sheets(1).Range(myCell.Address).Formula <> _
Sheets(3).Range(myCell.Address) = "Fault"
Sheets(3).Range(myCell.Address) = "Same"
Sheets(3).Range(myCell.Address) = "No Formula"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.