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.

Drat was hoping a simple Vlook would work...

Oh well thanks for coding I can work it out there atleast was just hoping
for more simple version... not that i could've worked that out hahaha. My VB
aint so great

Thanks again to you both for assist.

"JLatham" wrote:

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.