On 5/31/2018 1:06 PM, Brian Barker wrote:
At 12:32 31/05/2018 -0400, Vince Bonly wrote:
I find that often the template files available from
https://www.vertex42.com/ make use of alternate row shading, and I
would like to also. Example (copied from a CALC spreadsheet):
If you included an image here, it will have been removed by the
mailing list processor before your message was delivered. But I'm sure
readers will understand what you mean.
Does anybody know of a technique of placing shading on alternate rows
in CALC, as shown above? Does this require use of macros or is there
a function that can accomplish this?
o Click the rectangle at top left where the row and column headers
meet (or go to Edit | Select All, or press Ctrl+A) to select the
entire sheet (or as appropriate).
o Go to Format | Conditional Formatting... .
o For Condition 1, select "Formula is".
o In the box, enter MOD(ROW();2) .
o Click New Style... .
o On the Organiser tab, against Name, enter your new style's name -
perhaps "Shading" or "Pink"?
o On the Background tab, choose your background colour.
o OK.
o OK.
This gives shading on odd rows. If you want even rows shaded, change
the formula to MOD(ROW();2)-1 . By ticking Condition 2 and adding
another cell style, you can arrange to have both odd and even rows
shaded but differently.
Implementing alternate row shading in my spreadsheets might lead to
some difficulties, since I often use a sorting routine.
Sorting data should not disturb this shading technique.
I trust this helps.
Brian Barker
TNX again Brian; your suggestion worked perfectly.
I thought for a moment that my question was improperly phrased, as I
want the alternate row shading NOT done to an entire sheet. So, your
"(or as appropriate)" gave me confidence to enter the formula into
B3:G15. Worked like a charm!
Thee remains a problem, however. When attempting to sort with extended
selection data contained within B2:C14:
G L
A L
C O
D F
F N
L S
N V
O A
Q S
S Q
S W
V X
W D
the data within B2;C2 is not being included during the sorting
execution. I have seen this happen previously (shading not involved).
Any ideas on this?
Regards,
VinceB.
P.S. My pasting of B2:C14 data above might not appear? So I first pasted
the data into Notepad and then copied from Notepad to paste it below:
G L
A L
C O
D F
F N
L S
N V
O A
Q S
S Q
S W
V X
W D
Please note that "G" and "L" (present within B2;C2) are not being
included by the sorting routine for some reason. Is this a known issue?
