I'm comparing two lists and if a contract number equals that on another list I want to highlight the line in the original list. I have looked for ages on the web but everywhere that uses the range command seems to assume you know the row but I'm trying to use two variables as below.
If Trim(Worksheets("Cappuchino Data").Cells(iCappuchinoRow, 7).Value) = Trim(Worksheets("Subs Safety Net").Cells(iSubsSafetyRow, 3).Value) Then
> I'm comparing two lists and if a contract number equals that on another list > I want to highlight the line in the original list. I have looked for ages on > the web but everywhere that uses the range command seems to assume you know > the row but I'm trying to use two variables as below.
> If Trim(Worksheets("Cappuchino Data").Cells(iCappuchinoRow, > 7).Value) = Trim(Worksheets("Subs Safety Net").Cells(iSubsSafetyRow, > 3).Value) Then
The below code will highlight each row if 'Cappuchino Data' column G data match with the list in Subs Safety Net").Range("C1:C10")..Try and feedback
Sub Macro()
Dim rng1 As Range, rng2 As Range, cell As Range
Set rng1 = Worksheets("Cappuchino Data").Range("G1:G10") Set rng2 = Worksheets("Subs Safety Net").Range("C1:C10")
For Each cell In rng1 If Trim(cell.Text) <> "" Then If Not rng2.Find(cell.Text, , xlValues, 1) Is Nothing Then cell.Offset(0, -6).Resize(1, 31).Interior.ColorIndex = 15 End If End If Next
End Sub
If this post helps click Yes --------------- Jacob Skaria
> I'm comparing two lists and if a contract number equals that on another list > I want to highlight the line in the original list. I have looked for ages on > the web but everywhere that uses the range command seems to assume you know > the row but I'm trying to use two variables as below.
> If Trim(Worksheets("Cappuchino Data").Cells(iCappuchinoRow, > 7).Value) = Trim(Worksheets("Subs Safety Net").Cells(iSubsSafetyRow, > 3).Value) Then
Yes it does seem to have worked (i just need to confirm), however, my only question is, I don't always know the length of each of the lists so I can't put the range in. In this instance, I changed the values but it would be nice to have a generic routine that will work for any lists.
I havea routine that returns the number of lines in a list so using that would be good.
> I'm comparing two lists and if a contract number equals that on another list > I want to highlight the line in the original list. I have looked for ages on > the web but everywhere that uses the range command seems to assume you know > the row but I'm trying to use two variables as below.
> If Trim(Worksheets("Cappuchino Data").Cells(iCappuchinoRow, > 7).Value) = Trim(Worksheets("Subs Safety Net").Cells(iSubsSafetyRow, > 3).Value) Then
> Yes it does seem to have worked (i just need to confirm), however, my only > question is, I don't always know the length of each of the lists so I can't > put the range in. In this instance, I changed the values but it would be nice > to have a generic routine that will work for any lists.
> I havea routine that returns the number of lines in a list so using that > would be good.
> Many Thanks
> Steve
> "Steve" wrote:
> > Hi
> > I'm comparing two lists and if a contract number equals that on another list > > I want to highlight the line in the original list. I have looked for ages on > > the web but everywhere that uses the range command seems to assume you know > > the row but I'm trying to use two variables as below.
> > If Trim(Worksheets("Cappuchino Data").Cells(iCappuchinoRow, > > 7).Value) = Trim(Worksheets("Subs Safety Net").Cells(iSubsSafetyRow, > > 3).Value) Then
|
