VBA Vlookup with conditional formatting

[Lisa05]

Hello All,

I have a vba macro which vlookups cells in the same workbook between two tabs and replaces values in the origin cell to the value found. Is it possible to introduce conditional formatting that would indicate where vlookup actually replaced the cell value and which values were actually the same? Is it possible to combine conditional formatting with vlookup, like let's say first format cells that differ and then use vlookup to replace the value? Thank you in advance for help.

This is my code for vlookup:

Sub vlookup ()

Dim dataws As Worksheet
Dim searchdataws As Worksheet
Dim datalr As Long
Dim searchdatalr As Long
Dim datarange As Range
Dim x as Long

Set searchdataws = ThisWorkBook.Worksheets("Search")
Set dataws = ThisWorkBook.Worksheets("Data")

datalr = dataws.Range("B" & Rows.Count).End(xlUp).Row
searchdatalr = searchdataws.Range("B" & Rows.Count).End(xlUp).Row

Set datarange = searchdataws.Range("B2:D" & searchdatalr)

For x = 2 To datalr
On Error Resume Next
dataws.Range("D" & x).Value = Application.WorksheetFunction.VLookup(dataws.Range("B" & x).Value, datarange,3,0)

Next x

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Roughly how many rows of data do you have on each sheet?

 

[Lisa05]

Hi! That would be around 200 rows in each sheet

 

[Fluff]

Ok, how about

Sub Lisa()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Search")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 2).Value
      Next Cl
   End With
   With Sheets("Data")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Dic.exists(Cl.Value) Then
            If Dic(Cl.Value) <> Cl.Offset(, 2).Value Then
               Cl.Offset(, 2).Value = Dic(Cl.Value)
               Cl.Offset(, 2).Interior.Color = rgbCadetBlue
            Else
               Cl.Offset(, 2).Interior.Color = xlNone
            End If
         End If
      Next Cl
   End With
End Sub

 

[Lisa05]

OMG! It works perfect! Thank you so much for help

 

[Fluff]

You're welcome & thanks for the feedback.