How to lookup cell value and format

[ellafreemo]

Hi All,
I have an info sheet, which looks something like this [TABLE="width: 1477"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]USE CASE[/TD]
[TD]OUTPUT 1[/TD]
[TD]OUTPUT 2[/TD]
[TD]OUTPUT 3[/TD]
[TD]OUTPUT 4[/TD]
[TD]OUTPUT 5[/TD]
[TD]OUTPUT 6[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD]Plan 1[/TD]
[TD]Check 1[/TD]
[TD]Review 1[/TD]
[TD]Prototype[/TD]
[TD]Test 1[/TD]
[TD]Communicate[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD]Plan 2[/TD]
[TD]Check 2[/TD]
[TD]Review 2[/TD]
[TD]Type A[/TD]
[TD]Test 2[/TD]
[TD]Review[/TD]
[/TR]
[TR]
[TD]User 3[/TD]
[TD]Plan 3[/TD]
[TD]Check 3[/TD]
[TD]Review 3[/TD]
[TD]Type B[/TD]
[TD]Test 3[/TD]
[TD]Bypass[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD]Plan 4[/TD]
[TD]Check 4[/TD]
[TD][/TD]
[TD]Type A[/TD]
[TD]Test 4[/TD]
[TD]Iterate[/TD]
[/TR]
[TR]
[TD]User 5[/TD]
[TD]Plan 5[/TD]
[TD]Check 5[/TD]
[TD]Review 5[/TD]
[TD]Type B[/TD]
[TD]Test 5[/TD]
[TD]Conclude[/TD]
[/TR]
[TR]
[TD]User 6[/TD]
[TD]Plan 6[/TD]
[TD]Check 6 -zys[/TD]
[TD]Review 6[/TD]
[TD]Type A[/TD]
[TD]Test 6[/TD]
[TD]Run[/TD]
[/TR]
[TR]
[TD]User 7[/TD]
[TD]Plan 7[/TD]
[TD]Check 7[/TD]
[TD]Review 7[/TD]
[TD]Type B[/TD]
[TD]Test 7[/TD]
[TD]Prototype 2[/TD]
[/TR]
[TR]
[TD]User 8[/TD]
[TD]Plan 8[/TD]
[TD]Check 8[/TD]
[TD]Review 8[/TD]
[TD]Type A[/TD]
[TD]Test 8[/TD]
[TD]Review[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


However in the real spreadsheet the various output coLumns B-F, have colour formatting and comments and hyperlinks attached to various cells.
I want to have another sheet which will be like a generator where I can be able to lookup information from Row A in this sheet (table above), so with a drop down option I can pick a use case e.g user 8 and it will autopopulate the 6 output steps which i need to carry out. I have attempted this using vlookup but it only returns the cell values in the corresponding column B to F. All format (colours, hyperlinks and comments) associated with user 8 are not returned using a vlookup formula. Is there a VBA code I can run to perform a vlookup and return cell value with its colour format hyperlink and comment?

 

[Rick Rothstein]

Assuming the table looks like you showed in your posting (headers and all) and that the sheet it is on is named "Info", go to the sheet with your Data Validation drop down lists on it and put this event code in its code module...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Count = 1 Then
    Sheets("[B][COLOR="#0000FF"]Info[/COLOR][/B]").Columns("A").Find(Target.Value, Sheets("[B][COLOR="#0000FF"]Info[/COLOR][/B]").Range("A1"), xlValues, xlWhole, , , False, , False).Offset(, 1).Resize(, 6).Copy Target.Offset(, 1)
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.