Conditional Formatting in burst reports

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
139
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?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
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:
Code:
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
 
Last edited:

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 4, 2012
Messages
139
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,878
Messages
5,598,623
Members
414,248
Latest member
Tirali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top