Using Hlookup to return maximum of several values and return the source formatting

[cpollr]

Hi guys,

As the title suggests, I'm trying to find a term in another worksheet and return the associated value, (if there are several entries return the highest value), but then also return the cells formatting if it has it. I'm having trouble with the formatting as I believe this can only be done using fairly complex VBA.


I see Jerry Sullivan provided a great answer for vlookup here: http://www.mrexcel.com/forum/excel-...ookup-keep-cell-formatting-2.html#post3644436, however the kicker is, in order to get the maximum value I no longer use hlookup. the formula I'm trying to use is:

=MAX(IF(ISNUMBER(FIND(A3,RawData!$1:$1)),RawData!$5:$5))

Where A3 contains the search term, RawData is the worksheet with the data on that I want to return.

I can't seem to alter Jerrys UDF to fit my formula. Could anyone please help me to get my formula to also return the formatting of its target cell?

Many thanks,

Chris

 

[cwurfel]

I would look into Conditional formatting, since i believe it might be a way for you to get around this without involving VBA. Here is a link to start with:

Use a formula to apply conditional formatting - Excel

 

[cpollr]

I would look into Conditional formatting, since i believe it might be a way for you to get around this without involving VBA. Here is a link to start with:

Use a formula to apply conditional formatting - Excel

Thanks cwurful, but I don't think that's the solution for this one. I'm actually using this as part of a VBA module already, so VBA is preferred.