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:
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