Calc: Conditional format row colour if specific cell is odd/even

English
#1

I am keeping track of stats for a local rec hockey league. Each row in this particular play-by-play spreadsheet details a goal with assists or a penalty. I am using this in an effort to be able to generate reports showing individual player goal, assist, and penalty statistics, as well as some team stats.

All games are or will be in this spreadsheet so it becomes harder to read as it gets bigger. I would like to conditional format each game segment (ie. shade or colour every other range of game events). In other words, I want to shade or highlight every other game. The number of rows used differs in every game since the number of goals and penalties vary. Every row of data includes the game number. The column containing the game number is a named range, pbpgame. See the attached spreadsheet.

play by play.ods (39.8 KB)

#4

https://ask.libreoffice.org/search?q=alternate%20row%20color

#2

Something like this?
play by play.ods (56.7 KB)

#3

I used the game number as odd or even to decide which color to apply, hoping that the game numbers are sequential.

What I did:

  1. Select a large range from B4 to S480 (well below the last game, but you can put even more rows)

  2. Select the menu Format > Conditional > Condition

  3. In the condition window, select “Formula is” and enter the formula:

MOD($B4,2)=1

then select a New style and in the tab background color, choose the color you like.
The function MOD calculates the remainder of the division of the cell B4 by 2, so the answer is either 0 or 1. When it is 1, the condition is TRUE and the style is applied to all the cell on the odd lines. Note $B4 keeps the formula fixed on the row (for cell R4, the formula is the same), while when the row changes, it adapts the row number.

  1. Press the add button to add a second conditional format. The formula is a little bit different because I test whether the cell is empty and the remaining is 0. Same process, select a different color for the new style you define.
#5

Thank you Steph1 for responding. I completed steps 1-3 with no problem. (Nothing changed) Step 4 confuses me. What is the formula I need to enter in the second condition? Did something get cut off or am I missing something? You also mentioned a formula for R4, but I don’t understand what you meant…

#6

The second formula is visible in the attached example and it is:

AND($B4<>"",MOD($B4,2)=0)

Let me try to explain again how do the formulas work. So let’s take the cell B4 which contains 1 (displayed as 001). The first condition is TRUE (MOD(1, 2) is equal to 1), so the style Untitled1 will apply. The second formula is FALSE (MOD(1, 2) is not equal to 0) so the style Untitled2 will not apply. The result is that only the style Untitled1 applies.

For the cell B5, the first formula is evaluated as MOD($B5, 1)=0, which is TRUE.
Same reasoning for B6 to B10.

For B11, the first condition is FALSE (MOD($B11, 1) = MOD(2, 1) = 0), so the first condition is FALSE. The second condition is TRUE: $B11 is not empty and MOD($B11,2)=0, so the style Untitled2 applies.

For the cell C4, the first formula becomes… MOD($B4, 1)=0, no change! This is because the $ sign freezes the column in the formula. So C4 will have the same style as B4. Same for D4:S4.

For the cell C5, the first formula becomes MOD($B5, 1)=0 which is the same as the cell B5. Therefore, the cells of the same row will evaluate the same formula MOD($Bn, 1)=0, where n is the row number and therefore will have the same style.

Not sure that I am clearer… Let me know!

#7

Thank you again for taking the time to help me with this. I did not realize that you had attached a sample. I did get mine to work, but it took a bit of finagling.

It did not work at first even though my formulas matched your perfectly. In fact, I copied and pasted. I was using a different range that actually started at cell A4 instead of B4. I changed that and still nothing changed.

I then went in and changed my style from Light Grey to untitled1 and Pale Yellow to untitled2 and Voila! It worked.
I can’t say that I fully understand it, but it is working and that satisfies my immediate needs. Thank you again. This can now be marked as solved.

#8

Well then do it yourself! It’s just one click away:

image
image862×264 13.7 KB

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