Solved: Highlight vba code

[Sam1015]

I received the below vba code from a previous thread and it has worked great and proved most usefull!! I have come across a new issue, however, using this code. This code essentially matches up the target cells (i) from two sheets and pulls data from the input sheet(sheet 1) and puts them into the data sheet(sheet 2). I am now looking to highlight any target cells on the input sheet that were not found on the data sheet so I can easily identify what infomation wasn't transfered.

Private Sub Button2_Click()
Dim Source As Worksheet, Tgt As Worksheet
Set Source = Sheets(1)
Set Tgt = Sheets(2)
For i = 9 To Tgt.Cells(Rows.Count, 2).End(xlUp).Row Step 7
Set c = Source.Columns(1).Find(Cells(i, 2).Value)
If Not c Is Nothing Then
Tgt.Cells(i + 3, 12) = c.Offset(, 14)
Tgt.Cells(i + 3, 14) = c.Offset(, 17)
Tgt.Cells(i + 3, 16) = c.Offset(, 16)
End If
Next
End Sub

I greatly appreciate any help or direction anyone can give me.

Thanks!!

[Bob Phillips]

Private Sub Button2_Click()
Dim Source As Worksheet, Tgt As Worksheet
Set Source = Sheets(1)
Set Tgt = Sheets(2)
For i = 9 To Tgt.Cells(Rows.Count, 2).End(xlUp).Row Step 7
Set c = Source.Columns(1).Find(Cells(i, 2).Value)
If Not c Is Nothing Then
Tgt.Cells(i + 3, 12) = c.Offset(, 14)
Tgt.Cells(i + 3, 14) = c.Offset(, 17)
Tgt.Cells(i + 3, 16) = c.Offset(, 16)
Else
c.EntireRow.Interior.ColorIndex = 38
End If
Next
End Sub

[Sam1015]

Thanks XLD. I inputted the new code and when I ran it I received the following error message:

Run-time error '91:
Object variable or With block variable not set

Probably an error on my end. your help's much appreciated!

[Bob Phillips]

I didn't look closely enough.

See if this is better



Private Sub Button2_Click()
Dim Source As Worksheet, Tgt As Worksheet
Set Source = Sheets(1)
Set Tgt = Sheets(2)
For i = 9 To Tgt.Cells(Rows.Count, 2).End(xlUp).Row Step 7
Set c = Source.Columns(1).Find(Tgt.Cells(i, 2).Value)
If Not c Is Nothing Then
Tgt.Cells(i + 3, 12) = c.Offset(, 14)
Tgt.Cells(i + 3, 14) = c.Offset(, 17)
Tgt.Cells(i + 3, 16) = c.Offset(, 16)
Else
Tgt.Cells(i, 2).EntireRow.Interior.ColorIndex = 38
End If
Next
End Sub

[Sam1015]

Thanks xld. It works now but it highlights the targert sheet cells (worksheet 2). Any way for it to highlight the Source sheet(worksheet 1) instead?

thanks again.

[Bob Phillips]

Sam,

That is what I was trying to do originally, but it is not possible. If it doesn't find a match, then what can we highlight?

[Sam1015]

Oh ok, gotchya. Is it possible then to maybe put/list any Source cells(worksheet 1) that couldn't find a match on the Target sheet(Worksheet 2) on Worksheet 3? I'm just trying to be able to easily identify which source cells didn't find a match as the the number of rows of information on the source sheet will become very extensive.

Thanks!!!!

[Bob Phillips]

Maybe I am not getting this. Can you post an example workbook so as to eradicate any confusion?

[Sam1015]

I apologize if I'm not if I'm not being clear. Please see attached example workbook. If possible I would like to easily identify (highlight or any other way) which project numbers on Worksheet 1 did not find a match on worksheet 2.

Thanks!

[Bob Phillips]

Okay, see if this does it (it does highlight rather a lot)



Private Sub Button2_Click()
Dim Source As Worksheet, Tgt As Worksheet
Set Source = Sheets(1)
With Source
.Range(.Range("A3"), .Range("A3").End(xlDown)).Interior.ColorIndex = 38
End With
Set Tgt = Sheets(2)
For i = 9 To Tgt.Cells(Rows.Count, 2).End(xlUp).Row Step 7
Set c = Source.Columns(1).Find(Tgt.Cells(i, 2).Value)
If Not c Is Nothing Then
Tgt.Cells(i + 3, 12) = c.Offset(, 14)
Tgt.Cells(i + 3, 14) = c.Offset(, 17)
Tgt.Cells(i + 3, 16) = c.Offset(, 16)
c.Interior.ColorIndex = xlColorIndexNone
End If
Next
End Sub

[Bob Phillips]

Okay, see if this does it (it does highlight rather a lot)



Private Sub Button2_Click()
Dim Source As Worksheet, Tgt As Worksheet
Set Source = Sheets(1)
With Source
.Range(.Range("A3"), .Range("A3").End(xlDown)).Interior.ColorIndex = 38
End With
Set Tgt = Sheets(2)
For i = 9 To Tgt.Cells(Rows.Count, 2).End(xlUp).Row Step 7
Set c = Source.Columns(1).Find(Tgt.Cells(i, 2).Value)
If Not c Is Nothing Then
Tgt.Cells(i + 3, 12) = c.Offset(, 14)
Tgt.Cells(i + 3, 14) = c.Offset(, 17)
Tgt.Cells(i + 3, 16) = c.Offset(, 16)
c.Interior.ColorIndex = xlColorIndexNone
End If
Next
End Sub

[Sam1015]

It works perfect!! You've saved me a ton of time! yeh i have put all the highlighted cells into worksheet 2 and the source will update with even more projects every month so this code is really a huge time saver for me.

Thanks again for all your help!!

[Bob Phillips]

Glad we sorted it. Your original explanation was good, but seeing it made the solution so much clearer.