Conditional Formatting in burst reports

[DPChristman]

I have a large report with about 300 districts on it.

Each week, this report is burst out to the field, with each district getting only their stores.

I do not do the burst (don't know how, to be honest), so I didn't realize this was happening

When the file is burst out to the field, the person who does it excludes the header information from the report as unnecessary.

Unfortunately, the header info (company totals, average, and exception thresholds) how the conditional formatting for highlighting is determined.

Once that information is deleted, the highlighting becomes very wonky, and a lot of stuff is highlighted that shouldn't be and vice-versa.

Is there a was to maintain the cell highlighting without the conditional formatting?

 

[Krayons]

I have a large report with about 300 districts on it.

Each week, this report is burst out to the field, with each district getting only their stores.

I do not do the burst (don't know how, to be honest), so I didn't realize this was happening

I do this very same thing for my company.

When the file is burst out to the field, the person who does it excludes the header information from the report as unnecessary.

Unfortunately, the header info (company totals, average, and exception thresholds) how the conditional formatting for highlighting is determined.

Once that information is deleted, the highlighting becomes very wonky, and a lot of stuff is highlighted that shouldn't be and vice-versa.

Is there a was to maintain the cell highlighting without the conditional formatting?

Yes, there is. But unless I know more about how person X is generating these excel files and "blasting" them out to the appropriate people/districts, I can't really provide a good answer.

Again, this entirely depends on how person X is achieving this, but you would need to change the formatting of the cells, before they are copied (if they are in fact being copied), by mimicking the DisplayFormat and then deleting the conditional formatting.

Example:

Sub Keep_Format()
    Dim ws As Worksheet
    Dim mySel As Range, aCell As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set mySel = ws.Range("A1:A10")


    For Each aCell In mySel
        With aCell
          .Interior.Color = .DisplayFormat.Interior.Color
        End With
    Next aCell


    mySel.FormatConditions.Delete


     ' do the copying here
     ' and close workbook without saving

End Sub

 

[Jeffrey Mahoney]

Seems to me that the person doing the "Burst" (never heard that term) needs to include include the headers.

I found some code that can return the interior color or font color from the conditional formatting. Now, it would take some more code to turn all your conditional formatting into hard coded colors before it was sent out. It could be done.

Jeff

 

[DPChristman]

yeah email burst is our term. like I said, i don't do it.
We have two divisions, one uses the headers, one doesn't.
I looked at some of the reports distributed by the division that does include the header, and it looks fine.

My recommendation is going to be to keep the headers as well.

Thanks Guys!