Colour Coding Cells; For Loop Matching 2 Arrays

[SLogue]

Hi All,

I'm in need of a bit of assistance, I'm sure it's a very simple tweak but I'm struggling to get my VBA code to do what I want...

I'm trying to see if a number is present on another sheet, and if so colour it in. So I'm trying to save two arrays, one a 'stocklist' (built) and another an 'allocated' (assigned) list, and cross check them.

So far I've got:


Private Sub Workbook_Open()
Dim Assigned As Variant, Built As Variant, i As Long, j As Long

ActiveWorkbook.Sheets("Module Assignment").Select
Assigned = Range(Range("B2"), Range("B2").End(xlDown)).Value


ActiveWorkbook.Sheets("Build Sheet").Select
Built = Selection.Range(Range("C2"), Range("C2").End(xlDown)).Value

For i = 1 To UBound(Assigned)
For j = 1 To UBound(Built)
If Assigned(i).Value = Built(j).Value Then
cells(i + 1, 3).Interior.ColorIndex = 3
End If
Next j
Next i

End Sub


I'm getting 'Run-time error '9': Subscript out of range' so I assume it's a problem with trying to call a single value from each array to compare. However, after scrolling through various forum posts I can't seem to find a solution.

Any input would be really appreciated!

Sam

 

[SLogue]

Bump, still struggling to find solution.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG07Jun50
[COLOR="Navy"]Dim[/COLOR] Assigned [COLOR="Navy"]As[/COLOR] Variant, Built [COLOR="Navy"]As[/COLOR] Variant, i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Build Sheet")
    Built = .Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
 
 [COLOR="Navy"]With[/COLOR] Sheets("Module Assignment")
    Assigned = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] i = 1 To UBound(Assigned)
    [COLOR="Navy"]For[/COLOR] j = 1 To UBound(Built)
        [COLOR="Navy"]If[/COLOR] Assigned(i, 1) = Built(j, 1) [COLOR="Navy"]Then[/COLOR]
            .Cells(i + 1, 3).Interior.ColorIndex = 3
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] j
 [COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[SLogue]

Try this:-


Regards Mick

Hi Mick,

Thanks for your response. That certainly looks to be a step in the right direction, but it doesn't seem like it's matching the values within the cells.

Say I've got numbers 1:10 on the built sheet, and numbers 4 & 8 on assigned. It doesn't highlight numbers 4 & 8 on the build sheet, instead highlighting 1 & 2...

I tried to use .Value but that gives another error, so maybe not possible to do it in that way. Not sure if you've got any other ideas on what to do

Much appreciated,
Sam

 

[MickG]

Try this alternate solution:-
Code to highlight column "C" sht "Module Assigned" based on duplicates from sht "Build sheet" column "C" being found in column "B" sht "Module Assigned".

[COLOR="Navy"]Sub[/COLOR] MG10Jun40
[COLOR="Navy"]Dim[/COLOR] Built [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Assigned [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Build Sheet")
    [COLOR="Navy"]Set[/COLOR] Built = .Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Built
    .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]
 [COLOR="Navy"]With[/COLOR] Sheets("Module Assignment")
    [COLOR="Navy"]Set[/COLOR] Assigned = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Assigned
      [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
         Dn.Offset(, 1).Interior.ColorIndex = 3
      [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick