Compare two columns values with another two columns of same sheet

[sindhuja]

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]

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]

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]

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