Comparison problem

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi Friends,

Here is code to compare one cell in excelsheet with column in other excelsheet,
if it matches then it should get highlighted,but am getting problem during comparison.
and range(column) should be selected whichever i select ,can any one help me regarding this.



Sub Find_Matches()

Dim xColumn1 As Range
Dim yColumn1 As Range

Dim CompareRange As Variant, x As Variant, y As Variant

' Set CompareRange equal to the range to which you will
' compare the selection.
' Set CompareRange = Range("C1:C5")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.

Dim vFile As Variant
'Dim strfile, sh As String

'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select First Excelsheet", "Open", False)

'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If

'Open selected file
Workbooks.Open vFile

Set x = Application.InputBox("Select First cell to Compare", Type:=8)


Dim vFile1 As Variant
'Dim strfile, sh As String

'Showing Excel Open Dialog Form
vFile1 = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Second Excelsheet", "Open", False)

'If Cancel then exit
If TypeName(vFile1) = "Boolean" Then
Exit Sub
End If

'Open selected file
Workbooks.Open vFile1

Set y = Application.InputBox("Select Column to Compare", Type:=8)

Set CompareRange = Workbooks("y"). _
Worksheets("vFile1").Range("F1:F376")



' Set CompareRange = Workbooks("Details.xls"). _
' Worksheets("Crown Business Checking").Range("F1:F376")


For Each x In Selection

For Each y In CompareRange

If x = y Then
x.Offset(0, 2) = x
x.Offset(0, 0).Interior.Color = vbRed
x.Offset(0, 2).Interior.Color = vbRed
y.Offset(0, 0).Interior.Color = vbRed

End If

Next y
Next x
End Sub






1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What do you mean by what ever column you select ? Are you saying that when you run the macro it should prompt you for what two columns that you want to compare ? Could you go into more detail. And what is the error you are getting

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too