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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…