-
Sep 28th, 2018, 02:50 AM
#1
Thread Starter
New Member
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
-
Sep 28th, 2018, 06:04 AM
#2
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
-
Sep 28th, 2018, 06:49 AM
#3
Thread Starter
New Member
Re: Excel - cross referencing 2 work sheets
Originally Posted by westconn1
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
-
Sep 28th, 2018, 07:35 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|