LookupKeepColor functionality is not working when source & destination sheets are different

%3CLINGO-SUB%20id%3D%22lingo-sub-1687483%22%20slang%3D%22en-US%22%3ELookupKeepColor%20functionality%20is%20not%20working%20when%20source%20%26amp%3B%20destination%20sheets%20are%20different%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1687483%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Team%2C%3C%2FP%3E%3CP%3EMy%20requirement%20is%20to%20fetch%20the%20value%20along%20with%20its%20formatting%20from%20the%20source%20sheet%20cell%20to%20the%20destination%20sheet%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20LookupKeepColor%20functionality.%20Eventhough%20it%20is%20working%20when%20the%20source%20%26amp%3B%20destination%20sheet%20is%20same%2C%20but%20it%20fails%20when%20the%20destination%20sheet%20is%20different.%3C%2FP%3E%3CP%3ENote%3A%20Source%20worksheet%20-%26gt%3B%20%2214SEP%22%3B%20Destination%20worksheet-%26gt%3B%20%22Analysis%22%3C%2FP%3E%3CP%3EGive%20below%20is%20the%20process%20i%20followed%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep1.%20In%20the%20worksheet%20contains%20the%20value%20you%20want%20to%20vlookup%2C%20right-click%20the%20sheet%20tab%20and%20select%26nbsp%3BView%20Code%26nbsp%3Bfrom%20the%20context%20menu.%3CBR%20%2F%3EStep2.%20In%20the%20opening%26nbsp%3BMicrosoft%20Visual%20Basic%20for%20Applications%26nbsp%3Bwindow%2C%20please%20copy%20below%20VBA%20code%20into%20the%20Code%20window.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EVBA%20code%201%3A%20Vlookup%20and%20return%20background%20color%20with%20the%20lookup%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E'Vlookup%20and%20return%20value%20with%20font%20and%20interior%20color%3CBR%20%2F%3EDim%20I%20As%20Long%3CBR%20%2F%3EDim%20xKeys%20As%20Long%3CBR%20%2F%3EDim%20xDicStr%20As%20String%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ExKeys%20%3D%20UBound(xDic.Keys)%3CBR%20%2F%3EIf%20xKeys%20%26gt%3B%3D%200%20Then%3CBR%20%2F%3EFor%20I%20%3D%200%20To%20UBound(xDic.Keys)%3CBR%20%2F%3ExDicStr%20%3D%20xDic.Items(I)%3CBR%20%2F%3EIf%20xDicStr%20%26lt%3B%26gt%3B%20%22%22%20Then%3CBR%20%2F%3EWorksheets(%22Analysis%22).Range(xDic.Keys(I)).Interior.Color%20%3D%20_%3CBR%20%2F%3EWorksheets(%2214SEP%22).Range(xDic.Items(I)).Interior.Color%3CBR%20%2F%3EWorksheets(%22Analysis%22).Range(xDic.Keys(I)).Font.ColorIndex%20%3D%20_%3CBR%20%2F%3EWorksheets(%2214SEP%22).Range(xDic.Items(I)).Font.ColorIndex%3CBR%20%2F%3EElse%3CBR%20%2F%3EWorksheets(%22Analysis%22).Range(xDic.Keys(I)).Interior.Color%20%3D%20xlNone%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3ESet%20xDic%20%3D%20Nothing%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22com%22%3E'Put%20in%20a%20Module%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3EPublic%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xDic%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3ENew%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Dictionary%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EFunction%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20LookupKeepColor%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EByRef%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20FndValue%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EByRef%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20LookupRng%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Range%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EByRef%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xCol%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3ELong%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3EDim%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xFindCell%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Range%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EOn%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EError%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EResume%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3ENext%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xFindCell%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20LookupRng%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EFndValue%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xlValues%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xlWhole%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3EIf%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xFindCell%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EIs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22lit%22%3ENothing%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EThen%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20%20%20%20LookupKeepColor%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%22%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20%20%20%20xDic%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EAdd%20Application%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECaller%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EAddress%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%22%22%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3EElse%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ELookupKeepColor%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xFindCell%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EOffset%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22lit%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xCol%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E-%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22lit%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EValue%0A%20%20%20%20xDic%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EAdd%20Application%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECaller%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EAddress%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xFindCell%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EOffset%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22lit%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20xCol%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22lit%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EAddress%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EIf%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3EFunction%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAdd%20Reference%20'Microsoft%20Script%20Runtime'%20by%20Tools%20%26gt%3B%20References%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1687483%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Dear Team,

My requirement is to fetch the value along with its formatting from the source sheet cell to the destination sheet cell. 

I used LookupKeepColor functionality. Eventhough it is working when the source & destination sheet is same, but it fails when the destination sheet is different.

Note: Source worksheet -> "14SEP"; Destination worksheet-> "Analysis"

Give below is the process i followed -

 

Step1. In the worksheet contains the value you want to vlookup, right-click the sheet tab and select View Code from the context menu.
Step2. In the opening Microsoft Visual Basic for Applications window, please copy below VBA code into the Code window.


VBA code 1: Vlookup and return background color with the lookup value

 

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
'Vlookup and return value with font and interior color
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
xKeys = UBound(xDic.Keys)
If xKeys >= 0 Then
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
If xDicStr <> "" Then
Worksheets("Analysis").Range(xDic.Keys(I)).Interior.Color = _
Worksheets("14SEP").Range(xDic.Items(I)).Interior.Color
Worksheets("Analysis").Range(xDic.Keys(I)).Font.ColorIndex = _
Worksheets("14SEP").Range(xDic.Items(I)).Font.ColorIndex
Else
Worksheets("Analysis").Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Set xDic = Nothing
End If
Application.ScreenUpdating = True
End Sub

 

 

'Put in a Module
Public xDic As New DictionaryFunction LookupKeepColor(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As RangeOn Error Resume Next
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then    LookupKeepColor = ""    xDic.Add Application.Caller.Address, ""
ElseLookupKeepColor = xFindCell.Offset(0, xCol - 1).Value
    xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol -1).AddressEnd If
End Function

 

Add Reference 'Microsoft Script Runtime' by Tools > References

 

0 Replies