Here’s a VBA code snippet that performs a lookup (similar to VLOOKUP) and copies
both the value and the format (such as cell color) from the source to the
destination:
### Steps to Use the VBA Code:
1. Press Alt + F11 to open the VBA editor.
2. In the editor, go to Insert > Module.
3. Paste the following code into the module.
4. Press Alt + F8 to run the macro, or assign it to a button or shortcut as needed.
### VBA Code:
vba
Sub VlookupWithFormat()
Dim lookupValue As Variant
Dim tableArray As Range
Dim lookupColumn As Range
Dim resultCell As Range
Dim resultValue As Variant
Dim lookupRange As Range
' Set the lookup value
lookupValue = InputBox("Enter the lookup value:")
' Set the lookup table (adjust as per your needs)
Set tableArray = Application.InputBox("Select the lookup range:", Type:=8)
' Define the column where the lookup value is located
Set lookupColumn = tableArray.Columns(1) ' Assuming the first column is for
lookup
' Define the result cell where the result will be placed
Set resultCell = Application.InputBox("Select the cell where the result should
be placed:", Type:=8)
' Perform the lookup
On Error Resume Next
Set lookupRange = lookupColumn.Find(lookupValue, LookIn:=xlValues,
LookAt:=xlWhole)
' Check if the lookup value is found
If Not lookupRange Is Nothing Then
resultValue = lookupRange.Offset(0, 1).Value ' Getting the value from the
next column (adjust as needed)
resultCell.Value = resultValue
' Copy cell format
lookupRange.Offset(0, 1).Copy
resultCell.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Else
MsgBox "Lookup value not found!"
End If
End Sub
### How it Works:
- lookupValue: The value you're searching for (e.g., the lookup key).
- tableArray: The range of cells you want to search within.
- lookupColumn: The column that contains the lookup value.
- resultCell: The cell where you want to display the result.
- The macro will search for the lookupValue in the first column of tableArray. If
found, it retrieves the value from the next column (you can adjust which column to
pull data from by changing Offset(0, 1)), places that value in the resultCell, and
copies the format (e.g., background color, font, etc.) from the source cell.
### Note:
Make sure to adjust the offset in the code depending on your table structure. The
offset determines which column to pull the result from based on the found lookup
value.
==============================================================
use online2pdf.com for converting.. u must have used Ilovepdf.com
Can you please tell any shortcut for merging all the sheets
Rishabh
VSTACK or Power Query
Rishabh
VSTACK or Power Query
CA Vivek Gupta
How to use this
CA Vivek Gupta
How to use this
Rishabh
https://youtu.be/R2vkmdnfMHg?si=WpboXwf0jd_HULcB
YouTube
Combine multiple worksheets from the same workbook using Power Query
How to combine worksheets from the same workbook?
Here's one way to go about it using Power Query
https://youtu.be/qNz35j5ZevQ?si=Zv9xnwCPRNEe-qli
YouTube
VSTACK Excel - Quickly Combine Data from Multiple Sheets (Excel Noob Vs Pro)
Join 400,000+ professionals in our courses here 👉 https://link.xelplus.com/yt-d-
all-courses
Discover the power of VSTACK in Excel to dynamically combine data from multi...
