How to adjust this code to START AT ROW x, Normally B8:B works but not this time?

[ChrisOK]

2 Questions: (1) How do I adjust the range to start on row 8 on the "TO" sheet and start at row 5 on the "BOM Worksheet". When I've attempted to simply adjust "B" to "B8:B" or "P" to "P5:P" it skewed the matching capability.

Sub CompareAndHighlightGreen()
'THIS ONE LOOKS FOR CELLS THAT >>> DO <<< MATCH AND HIGHLIGHTS THEM
    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
    Dim isMatch As Boolean

    For i = 2 To Sheets("TO").Range("B" & Rows.Count).End(xlUp).Row
        isMatch = True
        Set rng1 = Sheets("TO").Range("B" & i)
        For j = 1 To Sheets("BOM Worksheet").Range("P" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("BOM Worksheet").Range("P" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                isMatch = False
                Exit For
            End If
            Set rng2 = Nothing
        Next j

        If Not isMatch Then
            rng1.Interior.Color = RGB(173, 255, 47)
            'rng1.Value = "Incorrect Name"
        End If
        Set rng1 = Nothing
    Next i
End Sub

2nd Question:
If I wanted to change it to highlight the row instead of a single cell, how do I adjust? I know there probably needs to be something added to specify Range.Row vs Range.Cell - but not sure how to incorporate that into this existing code.
(row meaning --- highlight from A until data ends at right)

 

[JLGWhiz]

Try

For i = 8 To Sheets("TO").Range("B" & Rows.Count).End(xlUp).Row 'Start on row 8

And

For j = 5 To Sheets("BOM Worksheet").Range("P" & Rows.Count).End(xlUp).Row 'Start on row 5

To highlight entire row.

rng1.EntireRow.Interior.Color = RGB(173, 255, 47)

 

[ChrisOK]

WOW - very kool!
Each worked perfectly -- with the exception of the entire row line -- it is going to 'infinity' -- is there something to be added
that can make it smart enough to stop the colorizing whereever the last bit of content resides? (rathering that going all the way out to "IV"?)

 

[JLGWhiz]

You might need to reverse your isMatch values, or restate your If statement to "If isMatch = True" .

 

[ChrisOK]

I think you might of misunderstood me...
All 3 lines of code work great.

The question is: Is there a way to keep the "entirerow" from going all the way from Column A to Column IV?
It'd be better if the colorization only turned the row where data existed ---

For example, if the data range is : A:J, then any row that it needed to color would only go as far as J
because it was smart enough to recognize that data only existed out through Column J.

As it stands above, it will colorize the entire row to infinity ------------->>>>>>>>>>>>>>>>>
Was wondering if it was easy to adjust it to only go where data exists?

Make sense?

 

[Michael M]

Maybe this

Sub MM1()
'THIS ONE LOOKS FOR CELLS THAT >>> DO <<< MATCH AND HIGHLIGHTS THEM
    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
    Dim isMatch As Boolean, lC As Integer
lC = Cells(2, Columns.Count).End(xlToLeft).Column
For i = 2 To Sheets("TO").Range("B" & Rows.Count).End(xlUp).Row
        isMatch = True
        Set rng1 = Sheets("TO").Range("B" & i & ":B" & lC)
        For j = 1 To Sheets("BOM Worksheet").Range("P" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("BOM Worksheet").Range("P" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                isMatch = False
                Exit For
            End If
            Set rng2 = Nothing
        Next j

        If Not isMatch Then
            rng1.Interior.Color = RGB(173, 255, 47)
            'rng1.Value = "Incorrect Name"
        End If
        Set rng1 = Nothing
    Next i
End Sub

 

[JLGWhiz]

I think you might of misunderstood me...
All 3 lines of code work great.

The question is: Is there a way to keep the "entirerow" from going all the way from Column A to Column IV?
It'd be better if the colorization only turned the row where data existed ---

For example, if the data range is : A:J, then any row that it needed to color would only go as far as J
because it was smart enough to recognize that data only existed out through Column J.

As it stands above, it will colorize the entire row to infinity ------------->>>>>>>>>>>>>>>>>
Was wondering if it was easy to adjust it to only go where data exists?

Make sense?

Change This:

rng1.Interior.Color = RGB(173, 255, 47)

To this

With Sheets("TO")
.Range(.Range("A" & rng1.Row), .Cells(rng1.Row, .Columns.Count).End(xlToLeft)).Interior.Color = RGB(173, 255, 47)
End With

That will only color cells up to the last one with data for that row.

 

[ChrisOK]

Michael - thx for the post, I tried it -- but it didn't do anything? I even attempted going F8 (line by line) but the curser never moved. Not sure why - but it didn't run.
JLGWhiz - I changed as directed and it highlighted out to column I perfectly (the end of where my data on this sheet existed)
Thanks GREATLY to both for the help!

 

[Michael M]

Glad you got it sorted....
Mine may have failed because it was finding the last column based on row 2

lC = Cells(2, Columns.Count).End(xlToLeft).Column

 

[ChrisOK]

very good - thanks for the update