Як скопіювати вихідне форматування комірки пошуку під час використання Vlookup в Excel?
У попередніх статтях ми говорили про збереження кольору фону при значенні vlookup в Excel. Тут, у цій статті, ми збираємось представити метод копіювання всього форматування комірок отриманої комірки при виконанні Vlookup в Excel. Будь ласка, виконайте наступне.
Скопіюйте форматування вихідного коду під час використання Vlookup в Excel із визначеною користувачем функцією

Припустимо, у вас є таблиця, як показано на знімку екрана. Тепер вам потрібно перевірити, чи вказане значення (у стовпці E) є у стовпці A, і повернути відповідне значення з форматуванням у стовпці C. Будь ласка, виконайте наступні дії, щоб його досягти.
1. На робочому аркуші міститься значення, яке потрібно переглянути, клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню. Дивіться знімок екрана:
2. На відкритті Microsoft Visual Basic для додатків вікно, скопіюйте нижче код VBA у вікно коду.
Код VBA 1: пошук і повернення значення з форматуванням
Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
Application.CutCopyMode = False
xKeys = UBound(xDic.Keys)
If xKeys >= 0 Then
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
If xDicStr <> "" Then
Set xRg = Application.Range(xDicStr)
xRg.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
End Sub
3. Then click Insert > Module, and copy the below VBA code 2 into the Module window.
VBA code 2: Vlookup and return value with formatting
Public xDic As New Dictionary
'Update by Extendoffice 20211203
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(External:=True)
End If
Application.ScreenUpdating = True
End Function
4. Click Tools > References. Then check the Microsoft Script Runtime box in the References – VBAProject dialog box. See screenshot:

5. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.
6. Select a blank cell adjacent to the lookup value, and then enter formula =LookupKeepFormat(E2,$A$1:$C$8,3) into the Formula Bar, and then press the Enter key.

Note: In the formula, E2 contains the value you will lookup, $A$1:$C$8 is the table range, and number 3 means that the corresponding value you will return locates in the third column of the table. Please change them as you need.
7. Keep selecting the first result cell, and then drag the Fill Handle down to get all results along with their formatting as below screenshot showed.

Related articles:
- How to vlookup and return background color along with the lookup value in Excel?
- How to vlookup and return date format instead of number in Excel?
- How to use vlookup and sum in Excel?
- How to vlookup return value in adjacent or next cell in Excel?
- How to vlookup value and return true or false / yes or no in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than
300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by
50%, and reduces hundreds of mouse clicks for you every day!
Sort comments by
#24738
This comment was minimized by the moderator on the site
#24739
This comment was minimized by the moderator on the site
Report
#24777
This comment was minimized by the moderator on the site
#24778
This comment was minimized by the moderator on the site
Report
#24779
This comment was minimized by the moderator on the site
Report
#24780
This comment was minimized by the moderator on the site
Report
#24980
This comment was minimized by the moderator on the site
#24998
This comment was minimized by the moderator on the site
#25009
This comment was minimized by the moderator on the site
Report
#25016
This comment was minimized by the moderator on the site
#25026
This comment was minimized by the moderator on the site
Report
#25027
This comment was minimized by the moderator on the site
Report
#26266
This comment was minimized by the moderator on the site
Report
#26267
This comment was minimized by the moderator on the site
#26268
This comment was minimized by the moderator on the site
#26269
This comment was minimized by the moderator on the site
Report
#26414
This comment was minimized by the moderator on the site
Report
#26415
This comment was minimized by the moderator on the site
Report
#26866
This comment was minimized by the moderator on the site
#26867
This comment was minimized by the moderator on the site
Report
#28180
This comment was minimized by the moderator on the site
Report
#28181
This comment was minimized by the moderator on the site
Report
#28672
This comment was minimized by the moderator on the site
Report
#28673
This comment was minimized by the moderator on the site
#28840
This comment was minimized by the moderator on the site
#29321
This comment was minimized by the moderator on the site
#29414
This comment was minimized by the moderator on the site
Report
#29415
This comment was minimized by the moderator on the site
Report
#31228
This comment was minimized by the moderator on the site
#34516
This comment was minimized by the moderator on the site
There are no comments posted here yet
Load More