Using Lookup Function based on Font Color

  • Thread starter Thread starter jstec68
  • Start date Start date
J

jstec68

Hello! I have several rows of data currently in which I would like to pull
data from. I could very easily use the LOOKUP function to do this. However,
both the lookup range and vector range are all numbers. I only want the
LOOKUP function to search for LOOKUP Values that are in a certain font color
(red). To my knowledge, the LOOKUP Value can only be a number, text, logical
value, or name or reference to a cell. Font Color is not one of my options.
Does anyone have any ideas? Am I missing an easy step? Thanks for your time!
 
I din't believe you can do that directly but with a workaround.

Put this function in a general module

Function fontcolour(rng As Range)
fontcolour = rng.Font.ColorIndex
End Function

In a column next to the lookup range enter the formula
=IF(fontcolour(A1)=3,A1,"")
This will extract all the red font values which you can use as the lookup
range

Mike
 
Back
Top