Is it possible to run a vlookup on font color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Also, is it possible to sort by the color of font in a cell or the background
color; e.g. sort all items in yellow first then red, etc.
 
For all cases you need some UDF that will get the colorindex of the text or
the cell.

To sort them, you would create a helper column with the colorindex returned
in, and sort by that column.

For the VLOOKUP, I think you will have problems. The index for the lookup
column is easy enough, it is the lookup table that is the problem, as any
colorindex function on that table will also return index for the offset
values, not the values themselves. So, although something like

=vlookup(COLORINDEX(B2),COLORINDEX(E2:F6),2,FALSE)

it will not work. You would need MATCH and INDEX, like

=INDEX(F2:F6,MATCH(colorindex(A2),colorindex(E2:E6),0))

As well as the page Frank referred you to, there is a colorindex routine at
http://xldynamic.com/source/xld.ColourCounter.html
 
As far as sorting by the colour of the font is concerned, it could be done
via Word.

Suppose you want to sort in the order red, orange, yellow, green.

Copy the cells to Word.

In Word:

Go to Edit, Replace.
Enable Wildcards.
In the Find field, set the font format to red.
Replace (?) with 1£\1
In the Find field, set the format to orange and replace (?) with 2£\1
In the Find field, set the format to yellow and replace (?) with 3£\1
In the Find field, set the format to green and replace (?) with 4£\1

Do the sorting according to the replaced text.

Replace [0-9]£ with nothing.

Copy back to Excel.

As far as sorting by background colour is concerned, the answer seems to be
not without Visual Basic. This seems to be a shortcoming in Edit Replace in
Word. There was a query about this in the Word section.
 
Back
Top