How to copy/paste conditional formatting from one range to another

[jackred]

Yesterday, I did what I am asking without any problem, but it seems I can’t make it work at all anymore for some reason.

I have both version 7.1.3.2 and version 6.4.7.2 and experience the same issue on both of them.

I am trying to apply a conditional formatting I defined on one range to another. A simple example is writing
| 1 | 2 | 3 |
| 4 | 3 | 5 |

then selecting the range 1;2;3, and apply the default colour scale, which will colour 1 in red, 2 in yellow and 3 in green. What I want is copy this formatting to the second line. My guess is to copy then special paste, uncheck everything except format and paste. This is confirmed by the answer here and the help here.
However, when I do so, the second line color doesn’t change at all. If I go inspect the different conditional formatting (Format>Conditional>Manage) I can observe that 3 new conditional formatting were created, one for each cell of the second line.

If I try to entirely copy the first line, and paste it (using ctrl+v, or a special paste and checking everything), to the third line for example, the conditional formatting of this third line will also be broken up per cell (and thus there won’t be any color).

If I try to use the “Clone Formatting” option, I have the same problem.

According to this post, the second answer exactly, if I apply the conditional formatting to 2 rows, I will able to paste it then. It works. If I apply manually the conditional formatting on the line 4;3;5, I can then select both line and paste them or special paste their formatting and it will correctly apply it. (It imply however that I have to do it two times by hand every time).

This question and its answers reference some bugs, said to be fixed but I don’t think it is similar to the problem I have.

The workaround I found and I think I was unconsciously using by default originally is to use cut (as I often cut then undo (or paste) to check that I properly copied).
If you cut the first line (with conditional formatting applied), and attempt to paste it anywhere, it will properly paste it (with the conditional formatting properly, the colour applied, etc). However, if you try to paste it once more, the conditional formatting will once again be broken up in multiple cell. It’s a “one paste” only solution. Even if you try to do a special paste instead of a normal paste the first time, the second and other paste won’t have a proper conditional formatting.
However, attempting to paste to multiple row (select multiple row, then paste or special paste) will works if it’s the first paste. Which means you can duplicate the conditional formatting (at the cost of your first line).
To avoid losing this first line, you can cut, then undo, then paste, and the first paste will works properly (not the other), which allow you to effectively duplicate the conditional formatting of a row, but in a very circumventing way.

It is normal that I have to use such a weird way to copy, or is there another simpler way not described in the help section? Or is a bug?

[PKG]

What does the condition (formulas) look like in the conditional formatting?

[jackred]

@pkg nothing specific, it’s just the basic conditional > colour scale choice.

(Edit: activated screenshots -AK)

[Fender]

may 2021 vs june 2023. still a problem. which is weird because how do people copy/paste conditional formatting when they add cells/row/columns or something changes. because even mouse1-dragging (left/right for columns) and filling out formulas, changes and creates new conditional formatting behind the scenes.

I can’t even create a macro, with selection already selected, to create a conditional format macro.
In this instance it’s also Color Scale (3 entries).

this is how the macro ends up looking:

sub green_low
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:ColorScaleFormatDialog", "", 0, Array())


end sub

[sokol92]

Please see this example.

[Fender]

idk man I’m just using normal tables. I don’t even know if I need pivot.
I tried to understand your sheet but I can’t make sense of it. when I unselect in the pivot table, the conditional formatting gets lost and the macros didn’t help.

this is what I have

every column is separate, so I have to apply formatting for every column. I can’t add rows or operate easily otherwise I have to redo the conditions. + there is no preset for colors when doing conditions, I have to select every time the three colors.
The only upside is that the color palette table stays the same, one less thing to select.

[LeroyG]

@jackred

trying to apply a conditional formatting I defined on one range to another

You can edit the cell range from C4:E4 to C4:E5; or C4:E4;C5:E5.