Calc: Creating conditional formatting acts weird

Whenever I create a new condition to change the color of a cell based on conditions with conditional formatting it changes the default style in the styles manager, somehow, and every sheet as well as new sheets then become that color as the background - the entire sheet and all sheets.

Every time I make a new condition it does this in one of my existing calc documents, not in others and not in new documents so I can’t create an example file to share. I have to go in to the styles manager, right-click Default Style > Modify and reset the background to “none” to correct it. It also does it to existing sheets only in cells I’ve recently edited.

Can someone explain why it’s doing this? Do I have a setting active somewhere or something? It doesn’t do it if I create a test sheet so I’m assuming I have something checked and active somewhere that’s doing it.

This is a company document I cannot share. Please don’t ask for the file, I’m not authorized to share it and this isn’t happening in new documents. An example file is not possible.

But maybe you can make a copy of the document, delete all compromising data and use fake data for the conditional formatting to work.

1 Like

… then probably no answer possible, since the problem is specific to a single document as per your own description and hence an investigation of that specific file is required.

1 Like

There’s over 10,000 cells that have data in them.
I’m hoping someone’s run in to this before and figured out how to correct it.

What file type are you using? (.ods, xls, xlsx, xlsm)
Which version of the LibreOffice are you using?
Where (which type and version of the Office suites) the file was created?
Are some macros inside/assigned to the specific document?
Can you share screenshots about the CF settings?


I keep software I use updated. The Libre suite just updated yesterday to 7.5. I had the latest stable version prior to yesterday.

Calc. I couldn’t tell you what version it was originally created in and with. I started the project this file is for around 5 years ago. It may have actually been originally created in Open Office Calc. I don’t remember, for sure positively, where and what version of what suite it was started in or what computer I have and use that it was started with if that’s what you’re asking.
I don’t and have never used MS office suites on my personal computers so it was either Open Office or Libre Calc several years ago.
Please clarify if this isn’t the type of info you’re asking for.

There’s only one macro I’m using for the entire book so far. Here is the code for it that was written by JohnSun:

Sub onBtnGoup(oEvent As Variant)
Dim sRngAddress As String
Dim oSheet As Variant
Dim oRange As Variant
Dim oData As Variant
Dim i As Long, j As Long
sRngAddress = oEvent.Source.getModel().Tag
oSheet = ThisComponent.getCurrentController().getActiveSheet()
oRange = oSheet.getCellRangeByName(sRngAddress)
oData = oRange.getDataArray()
If oData(0)(0) = 0 Then
For i = LBound(oData) To UBound(oData)
For j = LBound(oData(i)) To UBound(oData(i))
oData(i)(j) = 1
Next j
Next i
For i = LBound(oData) To UBound(oData)
For j = LBound(oData(i)) To UBound(oData(i))
oData(i)(j) = 0
Next j
Next i

End Sub

As you can see on line 8 it gets the active sheet. But for some reason the macro only works on one sheet. I can’t get it working on any other sheet.

If these aren’t what you’re asking for please share how I get the info you want to see and, yes, of course I’ll share screenshots of whatever settings you’d like to see.

These are the settings after I click the edit button on the first CF entry (C4:F20 Formula is $G4=1):

I believe I fixed it. There were a bunch of untitled styles in the style manager I deleted and I deleted and stopped trying to use the new style/conditional format I was trying to use then reset all of the styles and re-entered this CF and style. That corrected it and made all of the sheets the default colors again.

I’d like to use at least one more conditional formatting action on these sheets but if it’s going to do that again I’ll just avoid using more than one CF.

Untitled styles?! I never seen such thing in my documents. Your file (maybe) be dying. Maybe it was edited in MS Office (the lots of styles is characteristic symptom of it), or it is injured somehow.

Are there the TAG property set of the control element (a Button?) on every sheet?
Is there the macro assigned to all of the control elements?

Please prepare a sample file for the macro and the control elements, and share it here in a new question.

It’s fixed. It is not doing it anymore. Thanks for the help!
The untitled styles were styles I was fumbling around with creating the way I wanted them to be. I didn’t name them because I was testing them at first to get them to look the way I wanted to. Because I didn’t change the name field they took the name that’s in the name field by default: “untitled-1”, “untitled-2” and so on.