Compare same number in sheet1 and sheet2

[apple]

Hi all,

I would like to seek help for comparing the number in sheet1 whether available in sheet2. If sheet1 got the number in sheet2, it will highlight in sheet1 .Please refer my attachment. Thanks. hope to hear from you soon.

[sujittalukde]

See attached. USed Named range and Conditional formatting.

[apple]

Hi,

I not understand used name range and conditional format? Can u please explain. Thanks

[sujittalukde]

Select cells B6 to B10 and look at name box (leftmost dropdown list type, where normally cell address is shown) in the Original Data sheet. "list" name will appear
Go to next sheet and select B2, then Format\Conditional formatting, you will see the formula.

[apple]

Hi,

How about i want to do coding for comparing same number? Can u show me?
I want to click the button and it's start comparing the same number between 2 sheet. Thanks

[JimmyTheHand]

Try this:

[apple]

Hi Jimmy,

Thanks for your help. Have a nice day. Cheers

[apple]

Hi Jimmy,

Please do explain the code in red font.

For Each c In Data.Cells
Set Hit = plm.Find(c.Value, , xlValues, xlWhole, , , False)
If Not Hit Is Nothing Then c.EntireRow.Interior.ColorIndex = 3
Next
End Sub


Thanks. Hope you have a nice day.

[Bob Phillips]

It is looking for the value in the cell pointed to by the c variable within the range of cells pointed to by plm, and returning a range object, Hit, with the found cell.

[apple]

Hi,

How about the comma in blue font between Set Hit = plm.Find(c.Value, , xlValues, xlWhole, , , False). What is the comma means?

[Bob Phillips]

Just means it is skipping that value. Look at Find in VBA help.

[apple]

Hi,

Can anyone help me as below question:

Question 1:

I want to highlight the number for 11 digit (XXXXX-XXXXX) in length as i highlighted in red colour. Please refer my attachment. Please teach me how to achieve that.Thanks

[mdmackillop]

[VBA] For Each c In Original_List.Cells
Set Hit = Compare_List.Find(c.Value, , xlValues, xlWhole, , , False)
If Not Hit Is Nothing Then
Dim Test
If InStr(1, Hit, "-") > 0 Then
Test = Split(Hit, "-")
If Len(Test(0)) = 5 And Len(Test(1)) = 5 Then
c.Interior.ColorIndex = 3
End If
End If
End If
Next[/VBA]

[apple]

Hi,

Thanks for your help.

Can you explain the syntax as below in red font:

For Each c In Original_List.Cells
Set Hit = Compare_List.Find(c.Value, , xlValues, xlWhole, , , False)
If Not Hit Is Nothing Then
Dim Test
If InStr(1, Hit, "-") > 0 Then
Test = Split(Hit, "-")
If Len(Test(0)) = 5 And Len(Test(1)) = 5 Then
c.Interior.ColorIndex = 3
End If
End If
End If
Next

[Bob Phillips]

Dim Test

declares a variable (wastefully as it does it every iteration of the loop)

If InStr(1, Hit, "-") > 0 Then

checks if the variable Hit contains a dash

Test = Split(Hit, "-")

and if so, it splits Hit into an array of values delimited by dash

If Len(Test(0)) = 5 And Len(Test(1)) = 5 Then

If the first or second item in that array is 5 characters

[rory]

You could also just test if Instr(Hit, "-") = 6 and len(Hit) = 11

[mdmackillop]

Can you explain the syntax as below in red font:

Hi Apple
You should check the Help file first and come back with any particular clarification you require.

[apple]

Noted. Thanks