Solved: Application.Countif - separate workbook

[lienlee]

Hi Everyone,

I cant seem to get the last piece of code working.

The line of code beleow highlights to the correct cell, however, when I try to apply the range to have it when it follows "If Application.CountIf(newWB, wb1) <> 0 Then" for that excel cell when it does not equal 0 it does not work.


OldWholeSheet.Cells(1, 1).Interior.ColorIndex = 3


VS


'OldWholeSheet.Range(wb1).Interior.ColorIndex = 3




Public Sub UpdateMatchToSameWB(OldWholeSheet As Range, NewWholeSheet As Range)
Dim firstRange As Long
Dim secRange As Long
firstRange = OldWholeSheet.Cells(Rows.Count, "B").End(xlUp).Row
secRange = NewWholeSheet.Cells(Rows.Count, "B").End(xlUp).Row
Dim firstrang As String, secondrang As String
firstrang = "B1:B" & firstRange
secondrang = "B1:B" & secRange
Dim wb1 As Range
Set currentWB = OldWholeSheet.Range(firstrang) 'subset column
Set newWB = OldWholeSheet.Range(secondrang) 'subset column
Dim paramCopy As String
Dim paramPaste As String
Dim k As Long

For Each wb1 In currentWB 'for each value in column
If Application.CountIf(newWB, wb1) <> 0 Then
OldWholeSheet.Cells(1, 1).Interior.ColorIndex = 3
'OldWholeSheet.Range(wb1).Interior.ColorIndex = 3
'MsgBox wb1
End If
Next wb1

End Sub

[CatDaddy]

pass range by reference

[lienlee]

pass range by reference

Thanks! I actually found out what was wrong