Copy special format bug with conditional formatting between different sheets.

[Oppressed1]

First I should say that this seemingly inexplicable behavior MIGHT be "not a bug; it's a feature!" Fill me in if so.
Very easy steps to observe crazy behavior follow. Less than one minute for you to see the issue, I promise.

0 Start on the orange cell on sheet '2019'
1 Go to sheet '2017' and hit Control-C (copy cell B2 which is currently selected)
2 Return to sheet '2019' and copy special FORMATS over D2 (alt-E-S-T). It stays orange (I think that's a bug).
3 Go to sheet '2018' and hit Control-C (copy cell B2 which is currently selected)
4 Return to sheet '2019' and copy special FORMATS over D2 (alt-E-S-T). It loses the orange. It clears the C.F.
5 Go to THIS sheet '2019' and copy cell B2
6 On this sheet '2019' copy special FORMATS over D2 (alt-E-S-T). It loses the orange. It clears the C.F.
7 "Undo" in case you want to restore the orange and try other things.

First, I assume you see the same thing. If not, please inform. Otherwise…
Copying from sheet '2017' does not copy conditional formats (i.e. the absence); it either merges or ignores them.
Yet copying from sheet '2018' DOES copy conditional formats - that is, the absence of them. If you copy the format of a cell with no conditional format, the destination has no conditional format.
Copying from THIS sheet '2019' DOES copy conditional formats - that is, the absence of them.

Why? In fact, both cell B2 on sheet '2017' and cell B2 on sheet '2018' were created as a pure copy of cell B2 on sheet '2019', then merely changed to be values 2017 and 2018.

This seems to be a clear bug, going back for decades - xls on 2003, xlsx on 2016. Is there any evidence of this being seen or reported before?

Can anyone determine the specific scope of the bug? (Or, ahem, "feature")
For example, does it only occur when copying from the first of multiple sheets? (That's my preliminary theory)
For example, does it only occur when copying from the first of 3 or more sheets?
For example, does it only occur when copying from non-adjacent sheets?

Are there analogous issues - that is, is the problem more extensive than only with conditional formats? E.g. are other cell properties vulnerable?

[Oppressed1]

Hmm, every time I try to edit the post, the text typing window is empty (but the attachment remains).
Hmm, every time I tried to preview the post, the text typing window was empty (but the attachment remained).
I'm trying to add
step 4.5 "Undo" in case you want to restore the orange and try other things.

[Oppressed1]

As of now it shows that not one person has downloaded the macro-free demonstration worksheet. But if I hadn't included it, I'd almost certainly be told "Include a sample worksheet." Okay?

One minute - tops - to see the malady. I think you'll find it to be intriguingly or strikingly odd.

[AliGW]

It's only just over three hours since you first posted. For many forum members, a new day is only just dawning - it's breakfast time in the UK (still dark outside). Please be patient.

Regarding the blank post phenomenon - it's a known issue (bug). Just copy the text of the post, edit the post, paste and then edit.

[AliGW]

I think it's probably intended. If you copy and paste regular cell formatting (e.g. colour another cell on the 2019 sheet in the normal way then copy format from a clear cell from one of the other sheets), it works fine.

I don't have an answer for you, but can confirm what you describe in steps 0-2 - I don't have time right now to go into any more detail - sorry.

PS Which version of Excel are you using? I think your forum profile probably needs updating.

[Glenn Kennedy]

1 Go to sheet '2017' and hit Control-C (copy cell B2 which is currently selected)

2 Return to sheet '2019' and copy special FORMATS over D2 (alt-E-S-T). It stays orange (I think that's a bug).
Yes, but. The cell remains orange (but gains 2 dps as per 2017 B2).

3 Go to sheet '2018' and hit Control-C (copy cell B2 which is currently selected)

4 Return to sheet '2019' and copy special FORMATS over D2 (alt-E-S-T). It loses the orange. It clears the C.F.
No. It remains orange, and keeps 2 dps.

5 Go to THIS sheet '2019' and copy cell B2

6 On this sheet '2019' copy special FORMATS over D2 (alt-E-S-T). It loses the orange. It clears the C.F.
Yes. The CF formula has gone.


I will re-start & repeat step 4.

[Glenn Kennedy]

Now. It's behaving differently. 2017 and 2018 to 2019 - as I would have expected. CF remains in place. Remains orange, but gains 2 dps. This is what I would have expected as I assumed that paste formats would affect only the cell formatting - font, size, font colour, sub/superscript- in short all the properties set in the font and area fields. So, they doi exactly what I (at least...) expected.

However, if I copy formats from 2019 b2 to D2, it does indeed remove the CF. Completely and consistently. I would NOT have expected that!!

[Glenn Kennedy]

See here:

https://www.techrepublic.com/article...-cells-format/

[Oppressed1]

Thanks for testing and reporting, Glenn! Interesting that you believe that CF should not be inherited on a copy, whereas I think that it should - that is, I think that copying from another sheet should do what copying within a sheet does. Honestly I badly wish I could get a definitive resolution.

As far as inconsistency goes, oh brother. Or perhaps this is revealing for detectives determining the reason or extent of the problem: I tried on on XL 97, and results are slightly different for (internally named) Sheet1 to Sheet3, Sheet2 to Sheet3, and Sheet1 to Sheet3:
XL97: "bug", "bug", "works"
XL03, 16 "bug", "works", "works" (what I described in O.P.)

So I got on 97 what you got on 16

Note that I'm here calling NON-copying of CF "bug", whereas you opined that that might be called "works"

[Oppressed1]

See here:

https://www.techrepublic.com/article...-cells-format/

Thanks for that link, but note that it's speaking to a more basic issue, which is merely distinguishing between copy by value, and "regular Control-V" pasting which copies everything. What's going on in my workbook is focus on the case where you *do* copy formats; the question is whether conditional formats should be overwritten as well. We've identified an inconsistency on that behavior when it's from one sheet to another.

[AliGW]

Interesting that you believe that CF should not be inherited on a copy, ...

For what it's worth, I agree with Glenn on this.

[davesexcel]

When I first opened the workbook and followed your steps, both copy paste format did not change the d2 in sheet 2019

After I used the macro recorder to just copy and pastespecial formats and ran the code.
When I ran the code it did replace the CF with no CF.

Every time after that when I copied either sheets 2017 or 2018 into 2019 paste special FORMATS over D2 (alt-E-S-T) it replaced D2 with no CF.