Alternating Row formula creates havoc

[EarnestAl]

To create the style for colouring the alternating rows:

1. In the sidebar Styles pane in Cell styles, right click Default and select New
2. In the Organiser tab, give the new style a suitable name, e.g. Alternating Rows Green
3. Click on the Background tab, click the Colour button and set a colour.
4. Click OK. Do not set anything else, especially not number formats

To change the style of existing alternating row style

1. In the sidebar Styles pane in Cell styles, right click [alternating row style] and select Edit style
2. In the Numbers tab, at the bottom, click the button Standard to remove any settings in that pane that you applied. OK

Note that simply changing the style to General will not work because by changing that element of the style it has been disconnected from Default cell style

[andieguess]

I changed the style as you suggested and same issue. Here is a screen shot of what I see, the document in the background can not be formatted to date or currency. Even with your suggestions above.

When I change the code to “0” same thing, nothing can be formatted.

[EarnestAl]

In your screenshot, do you see the button Standard in the Number tab of the dialogue box? Click it and then click OK.

Do not touch anything else in the number tab

[andieguess]

Yes and when I click standard that is the result of clicking standard. I had put 0 in the format code as previously instructed. So I clicked standard as you instructed and I get the above screenshot. I took everything else out and sharing the tab I am working on. See Sample
Sample.ods (37.4 KB)

[andieguess]

Using your suggestion produces this…this is not what is being attempted here. Thank you for your suggestions though. I don’t know anyone who would want to have this…but if you do here it is.

[andieguess]

I’ve found this solution posted by:
anon73440385

Sep '19

Hello,

Solution 1: Reusable - Define your own Auto format

• Select range A1:D1 and set the color (e.g. white)
• Select range A2:D2 and set the alternate color (e.g. grey)
• Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here)
• Select range A1:D4 (important to have on uncoolorized line)
  (Updated as per comment of OP)
• Go to menu Format -> AutoFormat Styles
• Click Add - button, and deselect every option but not Pattern
• Provide a Name (e.g. AltWhiteGrey)
• Click button OK

Now you can use this Autoformat for any arbitray range (Selecting the range) and using Format -> AutoFormat Styles

Solution 2: - Use Conditional formatting

• Define a cell style with white background color (e.g. BGWhite)
• Define a cell style with grey background color (e.g. BGGrey)
• Add a conditional format using
  • Condition 1: MOD(ROW(),2)=1 - Apply Style 1 (BGWhite)
  • Condition 2: MOD(ROW(),2)=0 - Apply Style 2 (BGGrey)
  • Cell Range: Add here the cells, where you want to apply the format

“Drawback of this solution: Conditional Formats take precedence over direct formats.”

Per Anon, conditional formats is why I can’t format the date or anything else. So conditional formatting for alternating rows is not a correct way to do this.

I have followed the Solution 1 of auto format and it does work. However, it causes the machine or program to run very slow…Alas I am beginning to think I simply will not be able to have alternating rows, except to shade each row as I go. Is this the solution? Does anyone have a better way to do this that doesn’t clog the program down.

[andieguess]

Applying Autoformat causes the sheet to load in 1 minute and 18 seconds! That’s just for one sheet in the workbook with all other sheets removed! Imagine if I had 10 sheets in this workbook all with auto formatting in place…If the system didn’t stop responding I’d be surprised. Seriously, there has to be a better way to have alternation row colors.

[PKG]

@andieguess
Your problem starts with the Cell Style: Default.
Nothing is right there.

Normally, this Style should look like this:

As long as the Cell Style: Default is not in order
your efforts are doomed to failure.

[Villeroy]

cf_106845.ods (75.3 KB)

[andieguess]

I don’t want green, I don’t want bad, I want my own colors I choose.
I don’t know how to create hard formatting attributes that override the attributes of the cell styles.
So, when formatting those cell styles, what did you do that overrode the formatting attributes?

[andieguess]

Thank you for your contribution PK, it does very little in way of explaining how to correct the issue. Thank you for letting me know my efforts are doomed to failure, when I obviously don’t know how to set the Cell Style: Default or I would not be here in the first place.

[Villeroy]

Hit F11 for the list of cell styles.
Drop down “Status”.
RIght-click “Good”>Edit…
Change the colour.
RIght-click “Bad”.
Change the colour.

Ignoring styles is like ignoring the entire office suite. Each and every aspect of formatting in Writer, Calc, Draw and Impress is mainly about styles.

[andieguess]

well the application is not responding and freezing up now. After I did that autoformat styles the sample file is practically not usable.

[Villeroy]

@andieguess

well the application is not responding and freezing up now.

That should not happen, and it does not happen on my machines (Linux and Windows with LO 24.2.3)

[andieguess]

I have been waiting for 30 minutes and the file is hung. I’m over it. Thanks for your help. Seems like same issue all the other times I’ve downloaded this program, whether used on Windows or Mac, it hangs and has issues like this. I am wasting your time and mine. I wished it worked for me like it seems to work for everyone else.

[LeroyG]

@andieguess

program to run very slow

Do not apply the conditional formatting to all rows: 1:1048576. See @Villeroy comment below.

[LeroyG]

@andieguess

Sample

Follow @PKG comment: Alternating Row formula creates havoc - #12 by PKG.

The Alternating Row Green style is applied to A1:F1048576. Select this range and apply the clean Default style, then select A64:F1048576 and Clear Direct Formatting (Ctrl+M).
Manage the conditional formatting to extend to F63.
Apply a date formatting to dates in column A.

[Villeroy]

@LeroyG

Do not apply the conditional formatting to all rows: 1:1048576.

This would be bad practice, indeed. However, it does not freeze the application on my systems.

P.S. I copy an arbitrary row of my sample sheet, select the rest of the 4 columns and paste-special formats only. This takes a few seconds util the sheet becomes usable.

[andieguess]

Program to run slow??? What does that mean?

[andieguess]

Never understood his proposal in the first place as mentioned above. He states my cell styles are not correct, not how to correct them. When I follow his advice I get what I shared. So obviously the issue will not be resolved unless I am given the advice of how to correct the program to actually set to a standard default when clicking on standard default.