Adding column vlookup and color row

Question

I have work book which has 2 sheets Sheet1(No,Host,date,String,ID,TKT) Data ( ID & Name )

I want to create a single Macro which can add column with name and vlookup iD in data sheet and return name and change the color using id column

What i have

I have 2 separate Macros 1- Macro to change the color using id column

Worksheet code

Sub ChangeColor()
lRow = Range("E" & Rows.Count).End(xlUp).Row
Set MR = Range("E2:E" & lRow)
For Each cell In MR
Select Case cell.Value
Case "x12340"
cell_colour = 2
Case "x12341"
cell_colour = 6
cell.EntireRow.Font.ColorIndex = 4
Case "x12342"
cell_colour=6 
cell.EntireRow.Font.ColorIndex = 2
Case "x12343"
cell_colour=7 
cell.EntireRow.Font.ColorIndex = 2
Case "x12344"
cell_colour=8 
cell.EntireRow.Font.ColorIndex = 2
Case "x12345"
cell_colour=9 
cell.EntireRow.Font.ColorIndex = 2
Case Else
cell_colour = 1
cell.EntireRow.Font.ColorIndex = 4
End Select
cell.EntireRow.Interior.ColorIndex = cell_colour
Next
End Sub

2- Macro to add column in Sheet1 with header Name Sheet 1

Private Sub CommandButton1_Click()
Dim rngUsernameHeader As Range
Dim rngHeaders As Range

Set rngHeaders = Range("1:1") 'Looks in entire first row.
Set rngUsernameHeader = rngHeaders.Find(what:="ID", After:=Cells(1, 1))

rngUsernameHeader.Offset(0, 1).EntireColumn.Insert
rngUsernameHeader.Offset(0, 1).Value = "Name"

End Sub

What iam looking for

Single Macro which can do

1 & 2 and also vlookup iD from sheet1 in data sheet(Id & name) and return name on newly added column(Name) in sheet1

=VLOOKUP(E2,Data!A:B,2,FALSE)

Can you pls help on this

Thanks HR

Are you asking how to add a Vlookup over a range, in VBA? Also that first macro can be replaced entirely with simple Conditional Formatting, unless I'm missing something? Also, why do you have Case Else in the middle of the Select Case code? — BruceWayne
What do you mean by "add column with name and vlookup iD "? Maybe if you included a screenshot with what you have, and what you want as output? — pgSystemTester
asking how to add a Vlookup over a range, in VBA? - Yes Sir The reason why i haven't used conditional formatting is because i have too many id's here i just added few of them. Case else - Type :-) @BruceWayne — Harsha Reddy
I have written code for Adding column (2) and changing color (1)and now iam looking how to vlookup by vba (3) and merge all 3 into 1 code @PGCodeRider — Harsha Reddy
@HarshaReddy why not just copy in your click command macro into your your change color macro? again screenshots or clearer vision would be helpful. — pgSystemTester

John Bustos John Bustos · Accepted Answer · 2018-06-01T17:52:11

I'm very possibly not understanding your question, but if you just want to add in a VLookup function in the rngUsernameHeader.Offset(0, 1) column and run the change-color macro, you could change your Sub to look as follows:

Private Sub CommandButton1_Click()
Dim rngUsernameHeader As Range
Dim rngHeaders As Range

    Set rngHeaders = Range("1:1") 'Looks in entire first row.
    Set rngUsernameHeader = rngHeaders.Find(what:="ID", After:=Cells(1, 1))

    rngUsernameHeader.Offset(0, 1).EntireColumn.Insert
    rngUsernameHeader.Offset(0, 1).Value = "Name"

    ' Add in the VLookup:
    rngUsernameHeader.Offset(1, 1).Formula = "=VLOOKUP(E2,Data!A:B,2,FALSE)"

    ' Run the ChangeColor Macro:
    ChangeColor

End Sub

Hope that helps move you in the correct direction.

Adding column vlookup and color row

1 Answers