VBA for a Loop that Concatenates, Compares, and Highlights

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
Hi Y'all,

I've been working on this code throughout the day (I'm new to this) and I feel like I'm almost there but need a little help to finish.

So I have data in Sheet 1 and Sheet 2.

In sheet 1 cells C and D must be concatenated to compare to two different columns in Sheet 2 (The C&D from Sheet 1 could be found in either Column C in Sheet 2, Column E in Sheet 2, or not at all).

If C&D is found anywhere in Column C Sheet 2 I want the entire row that the C&D is located in to be highlighted, if found anywhere in Column E Sheet 2 I want the entire row that the C&D is located to be highlighted a different color, if neither proceed to the next C&D concatenate (in the next row down), compare again, and move on until all concatenates have been evaluated against both columns for a possible match.

If there are not matches I just had a MsgBox at the end of the IF.

Again I'm new to VBA, so I'm still starting to code for loops and dims. Any help and advice for the future (things to watch out for) is greatly appreciated! Any questions on what I'm describing let me know. My current code is listed below:

Code:
Sub Highlight()
Dim LValue As String
For j = 1 To 500
LValue = ThisWorkbook.Worksheets("Sheet1").Cells(j, 3) & ThisWorkbook.Worksheets("Sheet1").Cells(j, 5)
For i = 1 To 250
     If LValue = ThisWorkbook.Worksheets("Sheet2").Cells(i, 3) Then
          ThisWorkbook.Worksheets("Sheet1").Cells(j, 3).EntireRow.Interior.ColorIndex = 5
          
     ElseIf LValue = ThisWorkbook.Worksheets("Sheet2").Cells(i, 5) Then
          ThisWorkbook.Worksheets("Sheet1").Cells(j, 3).EntireRow.Interior.ColorIndex = 6
     Else
          MsgBox "Nothing to do for today!"
               Exit For
     End If
Next j
Next i
    
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
There are faster ways to do this, but since your data set is small, maybe just add a counter to your code - like this:
Code:
Sub Highlight()
Dim LValue As String, ct As Long, i As Long, j As Long
For j = 1 To 500
    LValue = ThisWorkbook.Worksheets("Sheet1").Cells(j, 3) & ThisWorkbook.Worksheets("Sheet1").Cells(j, 5)
    For i = 1 To 250
         If LValue = ThisWorkbook.Worksheets("Sheet2").Cells(i, 3) Then
            ct = ct + 1
              ThisWorkbook.Worksheets("Sheet1").Cells(j, 3).EntireRow.Interior.ColorIndex = 5
              
         ElseIf LValue = ThisWorkbook.Worksheets("Sheet2").Cells(i, 5) Then
            ct = ct + 1
              ThisWorkbook.Worksheets("Sheet1").Cells(j, 3).EntireRow.Interior.ColorIndex = 6
         End If
    Next i
Next j
If ct = 0 Then
    MsgBox "Nothing to do for today!"
Else
    MsgBox ct & " total matches highlighted"
End If
End Sub
 
Last edited:
Upvote 0
Code:
Sub Highlight()
    Dim LValue As String, j As Long
    For j = 1 To 500
        LValue = Sheets("Sheet1").Cells(j, 3) & Sheets("Sheet1").Cells(j, 5)
        With Sheets("Sheet1").Rows(j).Interior
            Select Case True
                Case Not Sheets("Sheet2").Range("C:C").Find(LValue) Is Nothing: .ColorIndex = 5
                Case Not Sheets("Sheet2").Range("E:E").Find(LValue) Is Nothing: .ColorIndex = 5
                Case Else: MsgBox "Nothing to do for today!"
            End Select
        End With
    Next 'i
End Sub
 
Upvote 0
There are faster ways to do this, but since your data set is small, maybe just add a counter to your code - like this:
Code:
Sub Highlight()
Dim LValue As String, ct As Long, i As Long, j As Long
For j = 1 To 500
    LValue = ThisWorkbook.Worksheets("Sheet1").Cells(j, 3) & ThisWorkbook.Worksheets("Sheet1").Cells(j, 5)
    For i = 1 To 250
         If LValue = ThisWorkbook.Worksheets("Sheet2").Cells(i, 3) Then
            ct = ct + 1
              ThisWorkbook.Worksheets("Sheet1").Cells(j, 3).EntireRow.Interior.ColorIndex = 5
              
         ElseIf LValue = ThisWorkbook.Worksheets("Sheet2").Cells(i, 5) Then
            ct = ct + 1
              ThisWorkbook.Worksheets("Sheet1").Cells(j, 3).EntireRow.Interior.ColorIndex = 6
         End If
    Next i
Next j
If ct = 0 Then
    MsgBox "Nothing to do for today!"
Else
    MsgBox ct & " total matches highlighted"
End If
End Sub

Why'd you insert a counter if you don't mind me asking?

I inserted the code into my module, and it seems to only be highlighting the empty cells at the end of the list. the 1 to 500 is an approximation as the actual sheet in question refreshes everyday, so some days there will only be 470 of the 500 rows with data. I know the two rows that should be highlighted today, but for some reason it is instead highlighting the 30 empty rows. Any thoughts on why it is doing this?
 
Upvote 0
Why'd you insert a counter if you don't mind me asking?

I inserted the code into my module, and it seems to only be highlighting the empty cells at the end of the list. the 1 to 500 is an approximation as the actual sheet in question refreshes everyday, so some days there will only be 470 of the 500 rows with data. I know the two rows that should be highlighted today, but for some reason it is instead highlighting the 30 empty rows. Any thoughts on why it is doing this?
I made the assumption that your code was working, but you wanted to avoid a message box on every row that didn't meet your criteria for highlighting. I added the counter so the message box could be removed from the loop and only appear once. I guess I made a bad assumption. Have to go offline now, but if no one has given you a solution when I return, I will try to revise your code.
 
Upvote 0
I made the assumption that your code was working, but you wanted to avoid a message box on every row that didn't meet your criteria for highlighting. I added the counter so the message box could be removed from the loop and only appear once. I guess I made a bad assumption. Have to go offline now, but if no one has given you a solution when I return, I will try to revise your code.

No worries, I appreciate any and all the help. Yes, my original code was not quite working, but I do appreciate the counter so that the message box could only appear once. Having to click enter 450+ times was rather annoying. Hopefully someone else takes a crack at it before you get back, if not, again I appreciate the help!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top