• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare 2 formulas

S

sdsurzh

Member
Hi,


I want to compare 2 formulas and if there is mismatch it should highlight(Conditional format)


Example:


A3 AA3 - No Changes

$A4 $A4 - Highlight ( It suppose to be $AA4)


Thanks,

Suresh Kumar S
 
Hi Suresh,


I Guess.. EXCEL is already doing the same.. by provide you INCONSISTENT FORMULA Symbol (green Triangle)..


Regards,

Deb
 
Hi Deb,


Thanks for your quick reply. My concern is it should get highlighted in conditional foramt. Is that possible?


thanks,

Suresh Kumar S
 
Hi suresh,


Are u trying to compare the cell reference in text format?


Say your data is as follows from A1 to B2:

[pre]
Code: 
A3	AA3
A4	A4
[/pre]

At C1 try this:


=IF(LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))-1)<>LEFT(B1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B1),""))-1),"Ok to proceed","Highlight") CTRL+SHIFT+ENTER and drag down


Kaushik
 
Hi Kaushik,


Thanks for your reply. I am sorry if i have not explained clearly.. Now i will explain in detail. Suppose Column A as some value which is working deponds upon some formula (A1=B1+C1) in the similar way column AA as the same value (AA1=AB1+AC1 but some times when we copy the fromula from A1 =$B1+C1 and paste in AA1 =$B1+AC1 the error is $B1 it should be $AB1. so i want to highlight those cells in conditional format. If you want i can upload the same workbook.


Thanks in advance,

Suresh Kumar S
 
Suresh,


Uploading the workbook would definitely help.


However, I have quick question here.


It looks like B1 , C1 and AB1, AC1 have some values already entered.


Once you copy the formula from A1, and paste it in AA1, it will take the reference of AB1 AND AC1 provided,Col B is not fixed (no $ sign before B).


So it looks like if you do not freez ColB, you will not face the problem. Am I correct?


May I know the reason behind freezing the Column B (when you are copying the formula right to the column)?


Kaushik
 
Hi Suresh ,


I doubt it can be done using formulae ; using VBA , the .FormulaR1C1 property will tell you whether one formula is different from another.


Narayan
 
Thanks Narayan & Kaushik for your replies.


Narayan,

Can you please give a vba code for such instance and i will upload the sample workbook by today evening.


Thanks,

Suresh Kumar S
 
Hi Narayan,


Please download the below file and provide a vba code.


http://www.2shared.com/file/8OlLx8Af/3_online.html


Thanks,

Suresh Kumar S
 
Hi Suresh ,


I am having problems with the file after downloading it ; can you download the file from your link , and see if you are able to open it ?


Narayan
 
Sir,


Can you please try the below links or let me know which site i can upload.


http://www.2shared.com/file/8OlLx8Af/3_online.html


http://www.2shared.com/fadmin/48604557/9eefb8ee/3_online.xlsx.html


Thanks,

Suresh Kumar S
 
Hi Suresh ,


Can you copy + paste this in the sheet section of your VBE ; I have defined two named ranges called Reference_Cell and Range_to_be_Checked ; you can either define these named ranges in your workbook , or you can replace their references in the code by absolute addresses.

[pre]
Code: 
Public Sub Compare_And_Highlight()
Dim rc As Range, ctbc As Range
Dim ref_formula As String

Set rc = ThisWorkbook.Worksheets("Sheet1").Range("Reference_Cell")
ref_formula = rc.FormulaR1C1

Set ctbc = ThisWorkbook.Worksheets("Sheet1").Range("Range_to_be_Checked")

ctbc.Select
Selection.Interior.Color = xlNone
For Each cell In Selection
If cell.FormulaR1C1 <> ref_formula Then
cell.Interior.Color = vbYellow
End If
Next
Set rc = Nothing
Set rtbc = Nothing
End Sub
[/pre]
Narayan
 
Hi Narayan,


Thanks for you vba code i have changed as per your suggestion. But while running i got the below message for the following lines


ref_formula = rc.FormulaR1C1


Error Message

type mismatch


Thanks,

Suresh Kumar S
 
Hi Suresh ,


Have you checked that the definitions have been done , the worksheet is selected ?


When this error appears , can you type in the immediate Window :


?rc.Address


and see what you get ? What does the reference cell contain ?


Narayan
 
Thanks Narayan,


I have changed the code suggested by you but all the cells are highlighted.


AA1 & AA3 should be highlighed since there is a difference when compared with A1 & A3.


Public Sub Compare_And_Highlight()

Dim rc As Range, ctbc As Range

Dim ref_formula As String


Set rc = ThisWorkbook.Worksheets("Sheet1").Range("Reference_Cell")

ref_formula = rc.Address


Set ctbc = ThisWorkbook.Worksheets("Sheet1").Range("Range_to_be_Checked")


ctbc.Select

Selection.Interior.Color = xlNone

For Each cell In Selection

If cell.FormulaR1C1 <> ref_formula Then

cell.Interior.Color = vbYellow

End If

Next

Set rc = Nothing

Set rtbc = Nothing

End Sub


Please advice.
 
Hi Suresh ,


I did not suggest that you change the code !


I suggested that when Excel displays the error message , type in rc.Address in the Immediate Window , and see whether the address matches with your reference cell address.


Secondly , I had requested you to see what the reference cell contains.


I had tried out the code I posted , and it worked on my computer ; I have no idea why it should not work on yours.


Can you upload your workbook ?


Narayan
 
Thanks Narayan for your valuable ideas and i am sorry for what i did. I am beginner in vba so i will upload the workbook can you please check and provide a solution.


http://www.2shared.com/file/kBEEqpPZ/3_1_.html


Thanks,

Suresh Kumar S
 
Hi Suresh ,


Can you check your file here ?


http://www.2shared.com/file/r5obf7XW/3-1.html


Check out the definition of the named range Reference_Cell.


Narayan
 
Hi Narayan thank you very much for your vba code. One more question without giving the name range,is it possbile to compare with A1 & AA1 formulas and it should highlight likewise till A3


Thanks,

Suresh Kumar S
 
Hi Suresh ,


The naming of ranges is not necessary in this case ; it is just a convenience.


Just bear in mind that the Reference_Cell is to be one cell , which has the correct formula ; the Range_to_be_Checked can be a single cell or it can be a multiple cell range , which needs to be verified to see whether all formulae in it are the same as the formula in the Reference_Cell.


You can use normal Excel cell addresses ; Reference_Cell can be replaced by a cell address such as $J$17 ; while Range_to_be_Checked can be replaced by a range address such as $B$11:$AB$38.


Narayan
 
You must log in or register to reply here.
Back
Top