PDA

View Full Version : VBA code fastest highlight of unique values



broman5000
03-27-2017, 06:54 AM
Hey guys/gals,

Please help!

Looking for the fastest VBA code that can highlight items in column B That do not exist in column A

Basically a loop of all data in column B and compare to whole column A until all column B data has been looked at

Column A is the master and may change each week
Column B changes in row length each week

If a cell in column B does not exist in entire column A, then highlight that cell in column B and continue the loop

I'd like to highlight the cell with red fill. I have attached the workbook at well

Thanks again and anything you can do to help would be great! Thanks!





MASTER Unique DROP P201711
Current Drop Week Unique Identifier


LIVEM37232401
LIVEM37232401


LIVER40232401
LIVER40232401


LIVEM35232402
LIVEM35232402


LIVER34232402
LIVER32232402


LIVET41232501
LIVET24232501


LIVEF31232501
LIVEF31232501


LIVEM36232601
LIVEM36232601


LIVER37232601
LIVER37232601


LIVEW43233301
LIVEW43233301


LIVES44233301
LIVES44233301


LIVEM20233302
LIVEM20233302


LIVER20233302
LIVER20233302

mana
03-27-2017, 07:10 AM
conditional formatting

=AND(COUNTIF(A:A,B2)=0,B2<>"")

jolivanes
03-28-2017, 12:08 AM
See attached


Sub ColorNonDups()
Dim w As WorksheetFunction
Dim n As Long, L As Long
Dim rA As Range, r As Range, v As Variant


Set w = Application.WorksheetFunction
Application.ScreenUpdating = False


n = Cells(Rows.Count, "B").End(xlUp).Row


Set rA = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)


For L = n To 1 Step -1
Set r = Cells(L, "B")
v = r.Value
If w.CountIf(rA, v) = 0 Then
r.Interior.Color = vbRed ' Shift:=xlUp
End If
Next L


Application.ScreenUpdating = True
End Sub

snb
03-28-2017, 01:02 AM
Simple, not fast:


Sub M_snb()
sn = Columns(1).SpecialCells(2)
sp = Columns(2).SpecialCells(2)

For j = 2 To UBound(sp)
If IsError(Application.Match(sp(j, 1), sn, 0)) Then Cells(j, 2).Interior.Color = vbRed
Next
End Sub

Incredibly fast:


Sub M_snb()
sn = Columns(1).SpecialCells(2)
sp = Columns(2).SpecialCells(2)

With CreateObject("scripting.dictionary")
For j = 2 To UBound(sn)
x0 = .Item(sn(j, 1))
Next

For j = 2 To UBound(sp)
If Not .exists(sp(j, 1)) Then Cells(j, 2).Interior.Color = vbRed
Next
End With
End Sub

mdmackillop
03-28-2017, 06:27 AM
Using Mana's formula

Sub Test()
With Cells(1, 1).CurrentRegion.Columns(2)
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(COUNTIF(A:A,B2)=0,B2<>"""")"
.FormatConditions(1).Interior.Color = 255
End With
End Sub

broman5000
03-31-2017, 07:02 AM
Using Mana's formula

Sub Test()
With Cells(1, 1).CurrentRegion.Columns(2)
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(COUNTIF(A:A,B2)=0,B2<>"""")"
.FormatConditions(1).Interior.Color = 255
End With
End Sub

Doesn't seem to be working...another wrinkle as well

just want to loop through column M and the entire range of L and highlight where each cell in column M is not located in range column L

Looping would start on row 2

wrinkle : If the value in column M begins with PNP, then don't worry about it (no highlight)

See attachment, thanks for the help!

mana
03-31-2017, 07:35 AM
conditional formatting
I think you don't need to use VBA

=AND(LEFT(M2,3)<>"PNP",COUNTIF(L:L,M2)=0,M2<>"")

mdmackillop
03-31-2017, 08:16 AM
@ Mana
I agree, but the OP asked for a VBA solution.

Incorporating your formula

Sub Test()
With Cells(1, 12).CurrentRegion.Columns(2).Offset(1)
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(LEFT(M2,3)<>" & Chr(34) & "PNP" & Chr(34) & ",COUNTIF(L:L,M2)=0,M2<>"""")"
.FormatConditions(1).Interior.Color = 255
End With
End Sub

snb
04-02-2017, 09:25 AM
@broman

Why ignoring the best solution ? (second in #4)