View Full Version : Compare two columns values with another two columns of same sheet
sindhuja
01-10-2013, 10:42 PM
Column A with ID and column B with Amount to be compared with column C ID and Column D amount.
If matches, we need to highlight the column A and B with some color.
Can this be done using formula or using VBA.
can some one assist me on this pls...:help
-sindhuja
CodeNinja
01-11-2013, 02:51 PM
Sindhuja,
There are lots of ways to handle this problem. I have attached a file showing two simple solutions.
The first solution would use a formula to handle your problem. The formula is in a "helper column" that says "Match!" if there is a match, then using conditional formatting, we can change the color of the cell. You can hide the column if you do not wish to see it.
The second solution is a macro. Very simple, it checks to see if they are a match and changes the fill color.
Hope this helps...
Good luck,
CodeNinja.
sindhuja
01-14-2013, 11:24 PM
Hi Ninja,
Thanks for your help!!
The formula you have provided cpmpare the column A and C, also column B and D if it matches then "Match" in column E.
But what i need is, i have to compare the value of cells in column C and D with the range A:A and B:B as there value may be in any row of column A and B.
So need to search in the entire column and highlight the column A and B if both matches.
Hope i made it clear now.
-Sindhuja
GreenDR
01-16-2013, 09:42 AM
I'm doubtful about this code, but its worth a try.
Sub Macro2()
Dim i As Long
Dim e As Long, f As Long
Dim a As Long
Dim nm As String, tnm As String
nm = ActiveWorkbook.Name
Range(Range("A1"), Range("A1").End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
tnm = ActiveWorkbook.Name
ActiveSheet.Paste
Range("F2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-5],RC[-4])"
Range("F2").Copy
Range("A2").End(xlDown).Offset(0, 5).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-3])"
Range("G2").Select
Selection.Copy
Range("C2").End(xlDown).Offset(0, 4).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Selection.End(xlUp).Select
'ActiveCell.Offset(0, 1).Range("A1").Select
'ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],R2C6:R53C6,0)"
'Range("H2").Copy
'ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1).Select
'Range(Selection, Selection.End(xlUp)).Select
'ActiveSheet.Paste
'Application.CutCopyMode = False
e = Range("F65536").End(xlUp).Row
f = Range("G65536").End(xlUp).Row
On Error Resume Next
For i = 2 To f
a = Application.WorksheetFunction.Match(Cells(i, 7), Range("$F$2:$F$" & e), 0)
If a <> 0 Then
Cells(a + 1, 1).Interior.Color = 52634
Cells(a + 1, 2).Interior.Color = 52634
a = 0
End If
Next i
On Error GoTo 0
Range(Range("A1"), Range("A1").End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows(nm).Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks(tnm).Close False
End Sub
Please give me your valuable feedback
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.