0

I previously asked a question regarding how to change font of matching items from an array on Sheet2. Now I want to go a little further and return the matching quantities to Sheet1 that matches the red fonted items. I really want to use vba to become more familiar and learn.

Here is my code below:

Dim i, j, x As Integer
Dim box1, box2, box3, box4, box5, box6, box7, box8, box9 As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box1 = Sheets(1).Cells.Range("C2")
box2 = Sheets(1).Cells.Range("C4")
box3 = Sheets(1).Cells.Range("C6")
box4 = Sheets(1).Cells.Range("F2")
box5 = Sheets(1).Cells.Range("F4")
box6 = Sheets(1).Cells.Range("F6")
box7 = Sheets(1).Cells.Range("I2")
box8 = Sheets(1).Cells.Range("I4")
box9 = Sheets(1).Cells.Range("I6")
Qty = Sheets(1).Cells.Range("D10")

'This refers to the checkbox
For i = 1 To 10 'Loop to find the checked box in sheet2

    'box 1 items and quantites
    If Sheets(2).Cells(1, i) = box1 And Qty Then 'Check for checked box
        For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))

            If Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i)) = Sheets(1).Range("D11:D30").Value And Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i)) <> "" Then
                x = x
                Else
                x = x + 1
            End If

            For Each d In Sheets(1).Range("B11:B30, F11:F30")
                If c = d Then
                    Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
                End If
            Next d
        Next c

Please note I'm just working on returning the quantities for box 1 so far. That's why I haven't posted the full code. Please see picture for better understanding of my lists I'm pulling from:Box Lists

Mesut Akcan
  • 899
  • 7
  • 19
mtiger1
  • 5
  • 3
  • Note that `Dim i, j, x As Integer` only declares `x As Integer` but all the others `As Variant` in VBA you must specify a type for **every** variable: `Dim i As Long, j As Long, x As Long`. Also I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. • Same for your box variables: Only `box9` is a `String` and `box1` … `box8` are `Variant`. – Pᴇʜ Dec 11 '18 at 08:10
  • Thanks for the note. I'll take that into consideration next time! Any word on the problem at hand? – mtiger1 Dec 11 '18 at 14:57

0 Answers0