Matching YTD Sheet against monthly sheet

    I have a file where I am trying to determine if two ranges on two different worksheets match (Sales order and Sales amount). My purpose is to keep track of sales commissions I have paid during the year.

    If Sales order abc (for example) for 10,000 on the Master worksheet (the year-to-date worksheet) appears in Feb(commissions for this month) with the sales order (abc) and 10,000, I would like the used range for that row to turn yellow.

    It has been a while since I have written code for a each loop.

    Again, the range would be on the Master YTD worksheet as an array of sales orders fro the entire year compared to the Feb worksheet which is an array of sales orders for the month of Feb. If their is a match for both Sales order and Sales amount on both sheets , I want all cells to turn yellow.

    Thanks for your help.

    when I run this code I get a "type mismatch" error.

    I was just experimenting to see if this macro worked for one Sales order and amount on the two different worksheets.


    Sub MatchingCells()
    If Sheets("Jan CMA bookings").Range("B122").Value And Sheets("Jan CMA bookings").Range("c122").Value = _
    Sheets("YTD CMA BOOKINGS").Range("A62").Value And Sheets("YTD CMA BOOKINGS").Range("B62").Value Then
    cell.Interior.Color = vbYellow
    End If

    If you paste this in the worksheet VBA project, it should do your second attempt.


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Sheets("Jan CMA bookings").Range("B122").Value = Sheets("YTD CMA BOOKINGS").Range("A62").Value _
    And Sheets("Jan CMA bookings").Range("C122").Value = Sheets("YTD CMA BOOKINGS").Range("B62").Value _
    Then
    Sheets("Jan CMA bookings").Range("B122").Interior.ColorIndex = 6
    Sheets("Jan CMA bookings").Range("C122").Interior.ColorIndex = 6
    Sheets("YTD CMA BOOKINGS").Range("A62").Interior.ColorIndex = 6
    Sheets("YTD CMA BOOKINGS").Range("B62").Interior.ColorIndex = 6


    Else
    Sheets("Jan CMA bookings").Range("B122").Interior.ColorIndex = 2
    Sheets("Jan CMA bookings").Range("C122").Interior.ColorIndex = 2
    Sheets("YTD CMA BOOKINGS").Range("A62").Interior.ColorIndex = 2
    Sheets("YTD CMA BOOKINGS").Range("B62").Interior.ColorIndex = 2
    End If
    End Sub


    I'm sure someone can come up with a better way though.


    Joel

    You're absolutely right,.
    It does work. I realize that my if then criteria was not set up properly.


    Next step would be to create a for Each loop for both worksheets.


    Anybody have any ideas?

    I liked Joel's selectionchange choice.... I wouldn't have thought of that, to be honest


    this should do the looping, although I doubt it's the most efficient method


    it does, however, assume your headings (sales order and amounts) are in row 1 on their respective sheets..... you should be able to change the code to suit your needs if they're not


    I've left my code as sitting in a normal module just in case having it kick in on every change proved too slow :


    <PRE&gt;
    Sub yellow_duplicates()
    Application.ScreenUpdating = False
    Dim janrows As Long
    Dim ytdrows As Long
    'ascertain respective row counts on each sheet
    Sheets("Jan CMA bookings").Select
    janrows = Range(Sheets("Jan CMA bookings").Range("B2"), Range("B2").End(xlDown)).Rows.Count
    Sheets("YTD CMA BOOKINGS").Select
    ytdrows = Range(Sheets("YTD CMA BOOKINGS").Range("B2"), Range("B2").End(xlDown)).Rows.Count
    'start loop on Jan sheet
    For q = 1 To janrows
    'switch loop to YTD sheet
    For t = 1 To ytdrows
    If Sheets("Jan CMA bookings").Range("B1").Offset(q, 0).Value = Sheets("YTD CMA BOOKINGS").Range("A1").Offset(t, 0).Value Then
    If Sheets("Jan CMA bookings").Range("B1").Offset(q, 1).Value = Sheets("YTD CMA BOOKINGS").Range("A1").Offset(t, 1).Value Then
    With Sheets("YTD CMA BOOKINGS").Range("A1")
    .Offset(t, 0).Interior.ColorIndex = 6
    .Offset(t, 1).Interior.ColorIndex = 6
    End With
    End If
    End If
    Next t
    Next q
    Application.ScreenUpdating = True
    End Sub
    </PRE&gt;

    I have something similar also to go in module, rather than sheet vb:


    Sub Compare()


    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim temp1 As String
    Dim temp2 As String
    Dim temp3 As String
    Dim temp4 As String


    Dim n As Long
    Dim x As Long


    Set WS1 = Sheets("YTD CMA BOOKINGS")
    Set WS2 = Sheets("Jan CMA bookings")



    temp1 = WS1.Cells(1, 1)
    temp2 = WS1.Cells(1, 2)
    temp3 = WS2.Cells(1, 2)
    temp4 = WS2.Cells(1, 3)


    x = WS1.Cells(1, 1).End(xlDown).Row
    n = 1


    Do While temp1 <> ""


    For i = 1 To x


    temp1 = WS1.Cells(i, 1)
    temp2 = WS1.Cells(i, 2)


    temp3 = WS2.Cells(n, 2)
    temp4 = WS2.Cells(n, 3)


    If temp1 = temp3 And temp2 = temp4 Then
    WS1.Cells(i, 1).Interior.ColorIndex = 6
    WS1.Cells(i, 2).Interior.ColorIndex = 6
    WS2.Cells(n, 2).Interior.ColorIndex = 6
    WS2.Cells(n, 3).Interior.ColorIndex = 6
    Else

    End If
    Next
    n = n + 1
    temp1 = WS1.Cells(1, 1)
    If temp3 = "" Then End
    Loop


    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login