Formula For Spreadsheets

  steviegee 08:06 15 May 2008
Locked

I have 2 spreadsheets. I need a formula that will tell me that a list of numbers in column A on sheet 1 appear anywhere on sheet 2 by changing its colour or something. Or maybe tell me where they are on sheet 2 in coloumn B or something. Thanks.

  VoG II 08:48 15 May 2008

I can't do this with a formula. Try this. Press Sub FindDups()
Dim LR As Long, i As Long, X As Variant
With Sheets("Sheet1")
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
X = .Range("A" & i).Value
If WorksheetFunction.CountIf(Sheets("Sheet2").UsedRange, X) > 0 Then .Range("A" & i).Interior.ColorIndex = 3
Next i
End With
End Sub




then close the code window using the X. Tools > Macro > Macros, highlight FindDups and click the run button. Cells in column A of Sheet1 that match any cell in Sheet2 will be coloured red.

  VoG II 08:51 15 May 2008

That came out wrongly - I'll try again.

I can't do this with a formula. Try this. Press ALT + F11 to open the Visual Basic Editor. Insert > Module and paste the code below into the white area on the right.



Sub FindDups()
Dim LR As Long, i As Long, X As Variant
With Sheets("Sheet1")
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
X = .Range("A" & i).Value
If WorksheetFunction.CountIf(Sheets("Sheet2").UsedRange, X) > 0 Then .Range("A" & i).Interior.ColorIndex = 3
Next i
End With
End Sub




then close the code window using the X. Tools > Macro > Macros, highlight FindDups and click the run button. Cells in column A of Sheet1 that match any cell in Sheet2 will be coloured red.

  steviegee 10:17 15 May 2008

Thanks VoG™ works a treat.

  Picklefactory 14:49 15 May 2008

:-)

  VoG II 14:57 15 May 2008

Just spotted an error :o(

Corrected code

Sub FindDups()
Dim LR As Long, i As Long, X As Variant
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
X = .Range("A" & i).Value
If WorksheetFunction.CountIf(Sheets("Sheet2").UsedRange, X) > 0 Then .Range("A" & i).Interior.ColorIndex = 3
Next i
End With
End Sub

  steviegee 15:02 15 May 2008

What was the error? The second of the 3 you sent seemed to work ok.

  VoG II 15:14 15 May 2008

LR = .Range("A" & Rows.Count).End(xlUp).Row

I originally omitted the . in front of Range.

I guess that you must have been on sheet1 when you ran the original code.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?