First off, I know, merged cells are the Devil and should be avoided at all costs. 
That said, I have no choice but to deal with them in this spreadsheet. I get a daily Excel product, which is generated from a third party system. I then have to make multiple formatting (color code, hide columns, make headers/footers) changes, populate the file with results. The cells must remain merged/unmerged as there were originally or when I upload the file back into the system, it will be rejected. I am trying to make a macro to perform all of the formatting. There are a couple hundred rows of data, some rows are unmerged across all of the columns, but most are 2-4 rows of cells merged in each of the columns, except for 3, which must remain unmerged. I have two questions that are driving me crazy trying to resolve:
1. I have written the conditional formatting rules to color the rows based the value of either of two columns. These columns are ones that may or may not contain merged cells. When the formatting runs on a row with merged cells, only the top row of cells in the columns with unmerged cells is colored, the subsequent rows below within the merged row remain unchanged. I understand this is because the merged row registers only as the top cell in the merged range and that the remaining merged cells have blank values. Is there a conditional formatting formula or VBA code that I can use to get the remaining cells within the row to look at the merged source cell and change.
2. My other issue is with merged cell rows and page breaks. I there any way to adjust the automatic page breaks via VBA so they are not placed in the middle of a merged row? I found a great script to use for Word, but absolutely nothing for Excel. I have seen the question asked a lot but no one seems to have a solution. I am hoping someone may have figured out a viable work around.
I had built a sample file to give a better visual idea of what I'm talking about, but unfortunately I don't seem to have permissions to attach a file. Thanks for any help.
Tom
That said, I have no choice but to deal with them in this spreadsheet. I get a daily Excel product, which is generated from a third party system. I then have to make multiple formatting (color code, hide columns, make headers/footers) changes, populate the file with results. The cells must remain merged/unmerged as there were originally or when I upload the file back into the system, it will be rejected. I am trying to make a macro to perform all of the formatting. There are a couple hundred rows of data, some rows are unmerged across all of the columns, but most are 2-4 rows of cells merged in each of the columns, except for 3, which must remain unmerged. I have two questions that are driving me crazy trying to resolve:1. I have written the conditional formatting rules to color the rows based the value of either of two columns. These columns are ones that may or may not contain merged cells. When the formatting runs on a row with merged cells, only the top row of cells in the columns with unmerged cells is colored, the subsequent rows below within the merged row remain unchanged. I understand this is because the merged row registers only as the top cell in the merged range and that the remaining merged cells have blank values. Is there a conditional formatting formula or VBA code that I can use to get the remaining cells within the row to look at the merged source cell and change.
2. My other issue is with merged cell rows and page breaks. I there any way to adjust the automatic page breaks via VBA so they are not placed in the middle of a merged row? I found a great script to use for Word, but absolutely nothing for Excel. I have seen the question asked a lot but no one seems to have a solution. I am hoping someone may have figured out a viable work around.
I had built a sample file to give a better visual idea of what I'm talking about, but unfortunately I don't seem to have permissions to attach a file. Thanks for any help.
Tom
