Forum Discussion
Remove conditional Format but retain cell format
Hi,
I have big sheets with too many conditional formats. I used conditional format for cell coloring, cell border, number format, text alignment etc. These I did through different conditions such "Format only cells that contain - Specific Texts" so eventually I have to remove those additional text to clean it up, but this will remove the format also as the condition fails for that particular cell. So I need a way to remove conditional format and retain format of those cells so that I can remove additional specific text which I used to impose some conditions.
I'm no good in VBA coding stuffs, so those "dims" are actually "not" packing much punch to me... 😞
Please help me with something that I can do it in a jiffy....
Try this trick:
Use print option and convert the excel to PDF. Convert it back to excel using adobe pro or some other tools. The format will be retained.
I found you can change your conditional formatting to also add text to the cells. just go to the custom part of number formatting for the text. whatever format you want, then a space, then SELECTALL or something. after your conditional formatting adds the text, you can go to "find" then with the value of SELECTALL choose "find all". it'll give you a list below of the cells. click on one result in the box, then hit "ctrl-a" on your keyboard to select all the cells with conditional formatting. choose whatever formatting you wanted to keep now. then you can clear the SELECTALL part from conditional formatting and you're done.
i realize that's a lot of steps, but if you're really set on getting it to work, this was the only way i could find, short of using a macro that would break my "undo" history.
- You can copy the affected cells to your clipboard and re-paste them. Refer here:
https://www.quora.com/How-can-I-remove-conditional-formatting-but-keep-the-color-formats-in-ExcelThanks PReagan,
But copy and paste the clipboard content works only for small amount of data as I noticed in my case always; for those big amount of data, pasting clipboard content doesn't include conditional format at all...!
