Alternating Row formula creates havoc

English
#1

Using OS MAC latest version, using Version: 24.2.3.2 (AARCH64) / LibreOffice Community
Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba
CPU threads: 8; OS: macOS 14.5; UI render: Skia/Metal; VCL: osx
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Following an answer to create condition formula ISEVEN(ROW()) apply color to row so that I can have alternating colors. Works great. However a new issue arises. I can not make date appear nor can I make currency work. All the text is correct, but can’t format dates as dates and currency as currency. Any ideas? Also can’t align columns to center after applying this formula to make the rows alternating colors.

#2

Yes, do not apply templates cellstyles to which you have assigned a number format.

ISEVEN_ROW.ods (78,7 KB)

1 Like
#4

Edited post: I found where to enter the format code “0”, but this changed nothing about being able to format the date to a date or currency to a currency or select the column right-left or center positions.
If I understand you correctly you are saying the solution of setting cell styles then applying that in a formula to use as a work around of applying alternating colors is incorrect way to apply alternating colors when needing to have any functionality of the document such as being able to set currency or dates?

#5

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

2 Likes
#6

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.

Screenshot 2024-06-15 at 6.14.53 AM
Screenshot 2024-06-15 at 6.14.53 AM1008Ă—928 269 KB
When I change the code to “0” same thing, nothing can be formatted.

#7

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

#8

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)

#9

Screenshot 2024-06-15 at 6.51.28 AM
Screenshot 2024-06-15 at 6.51.28 AM765Ă—460 79.8 KB

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.

#10

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.

#11

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.

#12

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

2024-06-15 14 52 07
2024-06-15 14 52 07738Ă—701 19 KB



Normally, this Style should look like this:

2024-06-15 14 54 37
2024-06-15 14 54 37738Ă—701 16.9 KB



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

1 Like
#13

cf_106845.ods (75.3 KB)

#14

Screenshot 2024-06-15 at 11.26.00 AM
Screenshot 2024-06-15 at 11.26.00 AM989Ă—453 148 KB

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?

#15

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.

#16

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.

1 Like
#17

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

#19

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

#20

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.

#21

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

#22

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.