lienlee
07-19-2011, 10:47 AM
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
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