Inheriting Format in Excel Cell Reference 1

[Nevermoor]

I have an aggregation workbook, which has a number of worksheets with rows of data like:

Name, value1, value2, . . ., globalsort

globalsort is calculated on these pages to allow ranking in aggregation. As the values change (thereby changing the globalsort value), the subtotal and grand total sheets automatically re-sort. The code I use, called OnChange in the basic sheets, is:
Subtotal Sheet:

Sub GetAlphaList()
    Cells.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ThisWorkbook.Worksheets("Aggregate").GetAlphaList
End Sub

Grand Total Sheet:

Sub GetAlphaList()
    Cells.Sort Key1:=Range("N2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

In the past, this code has worked fine. Now, however, the Name value in each of the original worksheets has a background color (unrelated to the data, so no conditional format) that must fill all the way through to the Grand Total sheet.

In the past, I simply put data in via direct reference (i.e. =(Sheet1!A1)) but that loses the name format. I attempted to just copy/paste the names and do the rest via reference as names/format colors are static, and that almost works. The sub-total pages work and re-sort correctly, but the data at the grand total level (which references the sub total page) does not.

Does anyone know how to either reference formatting or fix my sorting problem?

Thanks in advance,
Frank

 

[SkipVought]

Neither of the procedures that you posted GET anything. They SORT.

So neither have anythning to do with "Inheriting" anything.

You can, however, copy the formatted range and paste special - FORMATS over the unformatted range. Use you macro recorder.

What do you mean by "...fill all the way through to the Grand Total sheet?" How many sheets are there?

As I reread your post, it seems that you may be referring to ROW values in a Name column. Is that true? If so, you could loop thru the Name range and use each name to do a FIND in the formatted sheet, applying the FORMAT to the cell...

dim rName as range, rng as range
for each rName in UnformattedSheetObject.NameRange
  set rng = FormattedSheetObject.cells.find(rName.value) [gray]'this assumes that the name value ONLY appears in the name column and no where else[/gray]
  if not rng is nothing then
    'found it
    rng.copy
    rName.PasteSpecial xlPasteFormats
  end if
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Nevermoor]

I actually just got clearance to cut out the subtotal sheets, so the grand total just works.

The problem was that the subtotal sheets would sort, but the Name value in each row of the grand total sheet was fixed so it would become disjoint with the rest of that data row. It isn't elegant but it works...

Thanks