Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup based on Format
Stefi has given the definitive answer: VLOOKUP() will not work based on
format of cells, only on their contents/values. There are several ways to do this with a Macro, but it would be a coded macro, not a recorded one. First thing you need to know is what the 'colorindex' is for the shade of blue you are interested in. Easy way to determine that is to record a new macro while setting the shade of a cell to that color. Then use Tools | Macro | Macros to find that recorded macro and choose [Edit] to look at the code and see what number was used for the ColorIndex. You could then use a macro similar to this to get the blue-highlighted names into a list. You'll need to change things to fit your reality. "Sheet2" is the name of the sheet you want the extracted names to appear on, cell "A1" on that sheet is the first cell you want a found name to appear in. "SheetWithList" is the name of the sheet with the 1000 names on it and Range("A2:A1001") is the range that that list of names is in on SheetWithList. Sub FindTheBlues() Dim LongList As Range Dim anyCell As Object Dim LC As Integer Worksheets("Sheet2").Select Range("A1").Select Set LongList = Worksheets("SheetWithList").Range("A2:A1001") For Each anyCell In LongList If anyCell.Interior.ColorIndex = 5 Then ActiveCell.Offset(LC, 0) = anyCell.Value LC = LC + 1 End If Next End Sub If you're not familiar with the VB Editor and adding code, you could actually use the Macro you recorded earlier and substitute the code above between the Sub and End Sub statements for the code in that same area of the macro you recorded. Then just run that macro again. Just use the Tools | Macro | Macros to find that recorded macro and choose [Edit] process again to get back to it to alter the code. "Gareth - Network analyst." wrote: Hey Guys and Girls... I have a Question Is it possiable to do a Vlookup based on the Format of a cell. this is why i have worksheet 1 it has 1000+ names on and there are sevral highlighted names in several colors, what i want is to have Vlookups on worksheet 2 that will find all the names with Blue highlight and list them. can you do this??? and how would you.. Thanks in advance for assist. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
format decimals displayed based on results | Excel Discussion (Misc queries) | |||
change format of cells (bold, italic) based on one cell's answer | Excel Discussion (Misc queries) | |||
Import Format Using Vlookup | Excel Worksheet Functions | |||
formula based on format | Excel Discussion (Misc queries) |