PDA

View Full Version : Compare same number in sheet1 and sheet2



apple
08-03-2007, 09:01 PM
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
08-03-2007, 09:54 PM
See attached. USed Named range and Conditional formatting.

apple
08-03-2007, 11:23 PM
Hi,

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

sujittalukde
08-04-2007, 12:03 AM
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
08-04-2007, 12:11 AM
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
08-04-2007, 09:43 PM
Try this:

apple
08-05-2007, 05:13 PM
Hi Jimmy,

Thanks for your help. Have a nice day. Cheers :thumb

apple
08-06-2007, 07:06 AM
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
08-06-2007, 07:08 AM
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
08-06-2007, 08:00 AM
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
08-06-2007, 08:19 AM
Just means it is skipping that value. Look at Find in VBA help.

apple
08-08-2007, 05:43 AM
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
08-08-2007, 08:21 AM
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

apple
08-08-2007, 09:01 AM
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
08-08-2007, 09:34 AM
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
08-08-2007, 10:46 AM
You could also just test if Instr(Hit, "-") = 6 and len(Hit) = 11

mdmackillop
08-08-2007, 01:22 PM
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
08-11-2007, 10:45 AM
Noted. Thanks