I like to organize my sheets in Calc by giving each a dedicated background color. I also like to drag and drop entire lines from one location to another on the same sheet. (To manually sort lines by order of importance). However, whenever I drag (or copy and paste) a line and move it to the new location, it’s old location returns to the default background color of white. Is there a way to lock the background color to one solid color, unchanged by the dragging and dropping of lines?
I suppose it, those colors are not Sheet Background Color, but they are Cell bakground colors.
When you copy-paste or drag some cell content onto an another sheet the full cell content and the formatting properties will be moved onto the another sheet.
Tips:
If the cell contents are constants (but nor formulas) then you can use the Insert Unformatted Text feature
If you copied moved the formatting properties too, then you can easily reformat the dragged cells by Cell Styles. Just use the Styles instead of the direct (manual) formatting method.
Or you can use the Conditional format feature. Just use the function SHEET() as a condition. See in my sample file. This is a Style-based solution too.
C:\fakepath\CF_colorize based on the result function SHEET().ods
Second Attachment:
I’ve opened your file. When I copy a line from the middle, and paste it somewhere near the top, the spot where that line used to be turns white. Is there any way to make the spot it came from still stay red? (I.e., make every line red all the time, no matter what?)
Please upload your sample file (you can do it at your question), Just mod my sample file…
And please give us some more informations: what version of the LO are you using, and what file type are you using.
I’m not sure how to add a sample file the way you did. I have an .ODS file, with multiple sheets, and I change the background color of each sheet to something unique. (However when I copy a line from within the sheet, the location I take it from turns back to the default background of white).
using:
Version: 6.4.6.2
Build ID: 1:6.4.6-0ubuntu0.20.04.1
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3;
“I’m not sure how to add a sample file the way you did.”
Go back to your question, and edit it, by clicking the Edit link at the bottom of the question. You will see the Attachment icon at the header of the Edit box.
"(However when I copy a line from within the sheet, the location I take it FROM turns back to the default background of white). "
Are you using the Cut feature (Ctrl-X) for the “copy”? That function will cut (delete) the formatting properties too from the source cells. Use the Cell Styles to easier repair of the cell background color.
Yes, both Ctrl-x for or dragging and dropping the line will destroy it’s background color. I’ve added a sample file just for fun.
For reference: I pulled out my work computer, and in MS Excel, if you change a sheet’s “fill color” you can cut and paste lines without changing the sheet’s unbroken color.
But at present, there’s no way to duplicate that experience in LibreCalc? (removing the necessity of repairing the sheet color every time I want to cut and paste).
The Libreoffice Calc is not an Excel clone. There are different features and functions.
When you Cut or Drag the cell contents and formatting properties from a range/cell away, the applied Custon Cell Style will be removed, and the Default Style will be applied to the source range/cell. You can set the Default Cell Style to colorized background, but - unfortunately - only one Default Cell Style exists. You can not create/apply different Default Cell Styles per Sheet.
Workarounds:
- Use the Copy/PasteSpecial instead of the Cut/Paste or the dragging method.
- Write your own macros to “repair the localy applied Cell style” after the cutting/dragging automatically.
…
"I’ve added a sample file just for fun. "
Where is it?
My sample sheet should be attached now. Any idea where I could learn how to write a macro that repairs the locally applied Cell style after the cutting/dragging automatically? Thank you
You can record the simplier macros by the Macro Recorder. (It is better to WRITE your other macros based on the API functions, but this task requires some very simple macro, what the Recorder can record.)
The “MyCut” macro cuts all of cell contents together with the formatting proerties, but it pastes the formatting properties back into the source cell.
The “MyPaste” macro pastes only the text, number, date and the formula part of the cell content, but not the formatting properties.
See it in my second attachment at my Answer. The macros are stored in the file, and they are assigned to the Context menu of the Cells. (Right click on the cell and you will see the names of the macro routines)
The formatting properties of the cells was created by direct formatting method in your sample file. Use the Cell styles. The most valuable feature of the LibreOffice (and the Apache OpenOffice, Collabora Office) the STYLES. Use them: create and apply your own Styles.