Using LARGE & Keeping the Source Format

[MAMC84]

Hi all,

Through this forum I recently learned how to use the LARGE function. It's really great, but now I have a different issue. I'm using the LARGE function to sort a table of data. Each row of the data source is color coded, and as I sort via the LARGE function, I also need to pull the color code from the data source.

One row from the data source table is dynamic, so the sorted columns change with that one row. That being the case, I need the colors to change appropriately. I'm not sure what I need to do to make that happen. Basically, everytime the workbook is used, the data changes, so the sorting changes and the colors of each cell should change, but they don't. Any help would be great.
Thanks.

[dominicb]

Good afternoon MAMC84

There are numerous ways of finding the ColorIndex of a cell, however as these cell colours are changing dynamically, I would suspect that you're using conditional formatting. If this is the case then it's not really possible to pick up the colours, as it doesn't use the standard ColorIndex function.

You will have to use a way if determining what colour the cell is by applying the same conditions as the conditional formulae in a standard formula.

Chip Pearson provides some functions here, which interogate the FormatConditions object to check for a possible match - you might find this useful.
http://www.cpearson.com/Excel/CFColors.htm

HTH

DominicB

[MAMC84]

Hi DominicB,

Thank you for the link, I'll continue to read through it, but I have to say I have no idea what is required.

You said, "You will have to use a way if determining what colour the cell is by applying the same conditions as the conditional formulae in a standard formula."

Can you please elaborate?

Thank you.

[MAMC84]

DominicB & Any one else interested & willing:

Attached is a tiny sample of what I'm working with.

If at all possible, please help me to understand how I can utilize the Large function and pull the proper color. Column A in this attachment uses the large function to sort data in column C.

I suppose the workaround is to create macros to copy the data and sort, but if it can be automated without use of macros, it is better.

Thanks!

[dominicb]

Hi MAMC84

The colours appear to have been done by hand, not as conditional formatting, which means that my original answer is unsuitable. Also functions (even user defined functions) cannot be used to alter the format of a cell.

Therefore the only option you have open to you is to resort to VBA, which must be done after the event. The formula in place will alter dynamically as the values in column C change, but once this is complete the macro can colour the correct cells accordingly.

HTH

DominicB

[MAMC84]

I'll use macros and the users will have to suffer through them too. It's not that bad... Thank you for your help.

[MAMC84]

Thank you DominicB for giving me this VBA code. I thought I'd be able to figure out how to adjust it for my worksheet. Apparently, I am not that bright. Can someone please help? I have two tables with ranges, B3 to I11, and B15 to I23. The first table is pulling from L3 to S11, and the second pulls from L15 to S23. Thank you for helping!

Sub test()
a = Selection(1).Row
For n = a To Selection.Count + a
For f = a To Selection.Count + a
If Cells(n, 1).Value = Cells(f, 3).Value Then
Cells(n, 1).Interior.ColorIndex = Cells(f, 3).Interior.ColorIndex
End If
Next f
Next n
End Sub