Conditional formatting deleted when un-merging cells

[drivingforce]

Hi,

First of all. Thanks to anyone that might answer my question. I love this forum and have searched it many times for answers to all sorts of excel related question. However, this time around I could not find the answer. If this has been answered previously, I apologize for bringing it up again.

I have a spreadsheet where all cells in my active range are formatted as below:

#1) Cell value <> 0 - Fill cell with color X, Outline border
#2) Cell value = 0 - Outline border

So, to start with I have a range of empty/blank cells that all have borders outlining them, as per the #2 CF above. If I then write something in a cell, the cell changes color from blank/white to color X while at the same time keeping the outline/border. All good so far.
Sometimes I have to merge cells within this range (e.g. A1-H1) and after I enter something into that new "big" cell it changes colors as well as keeping the border. All good again.

But, and this is the problem, whenever I un-merge the selected cells, the CF for all cells, except the first/main one (A1) is gone. This means that all my cells in B1-H1 don't get the border after the un-merging. I then have to go back and manually apply my CF rules again, or copy from adjoining cells.

Is there a way of preventing losing my CF rules upon un-merging?

I hope my question is easy enough to understand. Let me know if you need any clarifications.

Thanks,
Lars

 

[Andrew Poulsom]

Welcome to MrExcel.

The answer to your question is no, but why do you need to merge/unmerge?

 

[drivingforce]

Thanks for your impressively quick reply. Much appreciated.

The spreadsheet handles car rental bookings and each column corresponds to one day, and each line a specific vehicle.
We merge cells whenever a booking is over two (or more) days. Unfortunately people change and cancel their bookings, hence the unmerging.

Really, the problem is purely related to the looks (and not functionality) of the spreadsheet, so we'll just have to live with it I guess