IrishChristof
New Member
- Joined
- Sep 17, 2018
- Messages
- 8
(screenshow below) I am looking to add icons against these footballers' names in column J, but I want to preserve the formatting from the lookup list G18:H27 (J7 shows an example of what I want it to look like).
Sample file here: lookup icons.xlsm
On another thread I found a way to do the cell formatting preserving using VBA code as follows:
And added a module like this:
Anyone know a workaround?
Sample file here: lookup icons.xlsm
On another thread I found a way to do the cell formatting preserving using VBA code as follows:
VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180706
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.EnableEvents = False
xKeys = UBound(xDic.Keys)
If xKeys >= 0 Then
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
If xDicStr <> "" Then
Range(xDic.Items(I)).Copy
Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
Else
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Set xDic = Nothing
End If
Application.ScreenUpdating = True
Application.CutCopyMode = True
Application.EnableEvents = True
End Sub
And added a module like this:
Code:
Public xDic As New Dictionary
'Update by Extendoffice 20180706
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Else
LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
End If
Application.ScreenUpdating = True
End Function
Anyone know a workaround?