Formula For Spreadsheets

  steviegee 08:06 15 May 08
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 08

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 08

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 08

Thanks VoG™ works a treat.

  Picklefactory 14:49 15 May 08

:-)

  VoG II 14:57 15 May 08

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 08

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

  VoG II 15:14 15 May 08

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.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5