Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is it possible to run a vlookup on font color
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. |
#2
|
|||
|
|||
Hi
only with VBA. See: http://www.cpearson.com/excel/colors.htm http://www.cpearson.com/excel/SortByColor.htm -- Regards Frank Kabel Frankfurt, Germany lephead wrote: 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. |
#3
|
|||
|
|||
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 -- HTH ------- Bob Phillips "lephead" wrote in message ... 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. |
#4
|
|||
|
|||
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. "lephead" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to enable font color on protected worksheet? | Excel Discussion (Misc queries) | |||
font color | Excel Discussion (Misc queries) | |||
How do I change the font color of my sheet tabs? | Excel Discussion (Misc queries) | |||
Formula results in font color change | Excel Worksheet Functions | |||
Can Formula Change the Font Color? | Excel Worksheet Functions |