Compare Columns and highlight if found?

[JimiH]

Hello

I have a sheet with 3 columns of data.

1st Master col

2nd Compare 1

3rd Compare 2

I want to highlight all values in the master col that appear in either or both compare 1 ,compare 2 columns.

If the value only appears in Compare 1 then highlight in green in master Col.

If the value only appears in Compare 2 then highlight orange in master Col.

If the value apears in both Compare 1 & compare 2 then highlight Red in master col.

Hope this makes sense.

Thanks

Geoff

 

[mirabeau]

hi Geoff,

If you don't mind a VBA macro, perhaps try out this one.
The columns are laid out as Master Column =ColumnA of your worksheet, Compare1 = ColumnB and Compare2 as ColumnC.

Sub comparecols()
Dim d1 As Object, d2 As Object, e
Dim mastcol As Range
Dim comp1, comp2
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")

With Intersect(Range("A:C"), ActiveSheet.UsedRange)
    Set mastcol = .Resize(, 1)
    comp1 = .Columns(2)
    comp2 = .Columns(3)
End With
For Each e In comp1: d1(e) = 1: Next e
For Each e In comp2: d2(e) = 1: Next e
For Each e In mastcol
    If Len(e.Value) > 0 Then
    If d1.exists(e.Value) And d2.exists(e.Value) Then
        e.Interior.Color = vbRed
    ElseIf d1.exists(e.Value) Then
        e.Interior.Color = vbGreen
    ElseIf d2.exists(e.Value) Then
        e.Interior.ColorIndex = 46
    End If
    End If
Next e

End Sub

 

[JimiH]

Hi Thanks, i'll keep this for future reference.

I used the MATCH function in the end along with some conditional formatting.

=IF(ISERROR(MATCH(A2,B$2:B$2766,0)),"No","Yes")

A2 contains the search criteria
B$2:B$2766 is where to search

("No","Yes") ,couldnt find "No" ,I found it "Yes".

i pasted the function into "D1" then filled down.
So now it starts with A2 then moves down A3 ,A4 etc untill all of the master column has passed a value to the search list.

Thanks again.

Geoff