Results 1 to 4 of 4

Thread: Excel - cross referencing 2 work sheets

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    4

    Question Excel - cross referencing 2 work sheets

    Hi,

    I am getting nowhere trying to figure this one out myself so I'm here looking for your help please.

    I have 2 work sheets in excel. One called Production contains data including a column of job ID's. The second sheet called Components just has the job ID's. When I put a job ID into the Components sheet it looks for the matching ID on Production sheet and copies the cell background colour to the ID in Components. This works great with the current code but the problem I'm having is if there are more than one entry with the same ID it only matches the colour of the last entry in Production. I need it to only look at the first entry nearest the top of the sheet as I have the data in Production with the latest entries first (date in descending order). Any ideas?

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim wsHighlight As Worksheet
        Dim wsData As Worksheet
        Dim rngColor As Range
        Dim rngFound As Range
        Dim KeywordCell As Range
        Dim strFirst As String
    
        Set wsHighlight = Sheets("Production")
        Set wsData = Sheets("Components")
    
        With wsData.Columns("A:M")
            For Each KeywordCell In wsHighlight.UsedRange.Columns("B").Cells
                Set rngFound = .Find(what:=KeywordCell.Text, LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
                If Not rngFound Is Nothing Then
                    strFirst = rngFound.Address
                    Set rngColor = rngFound
                    Do
                        Set rngColor = Union(rngColor, rngFound)
                        Set rngFound = .Find(what:=KeywordCell.Text, LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
                    Loop While rngFound.Address <> strFirst
                    rngColor.Interior.Color = KeywordCell.Interior.Color
                End If
            Next KeywordCell
            
        End With
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel - cross referencing 2 work sheets

    if you want the first instance, do not use xlprevious as search direction, change to xlnext and specify after argument as last cell in used range, so it will start at the beginning, or if you are sure the first instance would not be in the first cell, you can use after the first cell

    After Optional Variant. The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    4

    Re: Excel - cross referencing 2 work sheets

    Quote Originally Posted by westconn1 View Post
    if you want the first instance, do not use xlprevious as search direction, change to xlnext and specify after argument as last cell in used range, so it will start at the beginning, or if you are sure the first instance would not be in the first cell, you can use after the first cell
    Thanks for looking at this. I've tried doing this and it see's the first instance but continues until it sees the last, I just can't seem to get it to stop after the first instance is found

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel - cross referencing 2 work sheets

    I need it to only look at the first entry nearest the top of the sheet
    if you are trying to find all matching ids, which appears to be what the code does, then it should not matter in which order you search, but as you specify you only want the first entry, i do not see why you are using a DO loop that should find and then colour all matching IDs, even if it is not working correctly

    I just can't seem to get it to stop after the first instance is found
    remove or comment out the DO loop (5 lines of code)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width