Forum Discussion
Pasting formats from conditional formatting
Hi,
This is going to be a little hard to explain but I'll try my best and use photos.
I have a conditional formatting custom rule setup to highlight a row of cells based on an If function.
I want to copy the data that includes these highlighted rows and paste it onto another workbook that will be saved as CSV so it can be uploaded to another software.
My problem is when I paste the data into the new workbook, none of the rows are highlighted anymore.
I have tried to paste special with values and formatting, source formatting, merge conditional formatting, and nothing works. I'm somewhat familiar with VBA but couldn't figure it out with that either.
So, is there a way to copy highlighted cells, that were highlighted with conditional formatting, and paste those cells into a new workbook while keeping the cells highlighted?
Below shows the conditional formatting rule and the data that will be copied.
Then the selected cells to copy.
Then the pasted cells in a new workbook using Keep Source Formatting. No cells are highlighted.
To show the format rule and the dataTo show the data selected to copyThe result when pasted (using keep source formatting) and no highlighted cells
4 Replies
Hi J_Wils
I think the issue here is the fomatting rule. According to the first screenshot, the rule checks for blank cells in column $U and also refers to $Z, $AA and some more columns.
So when you copy over the highlighted records to the new workbook, the formatting rule will still check those references in $U, $Z and so on. And I expect that they do not exist in the target sheet, therefore the formatting rule does not work.
So you could either make sure, the target sheet has exactly the same structure, references etc. as the source sheet.
Or you just need to apply manually the background color right after copying, when the rows are still selected. And ignore the formatting rule.
- Ah, ok thanks. Yeah neither of those options would work for what I want to do but thanks for the advice.
I still think it's weird excel doesn't treat conditional formats the same as manually applying formatting. Or at least it's interesting that copying a format that is done through conditional formatting doesn't actually copy the formatting itself but just the rule.It depends on how you use absolute/relative references in conditional formatting rule. In general it shall work.
