Is there a way to add background color to alternate rows of a table/spreadsheet?

[lewwarren]

Adding color to alternate rows of a table/spreadsheet

[JohnSUN]

With Conditional Formatting.

Select range and apply format with formula

ISODD(ROW())

or

ISEVEN(ROW())

or both

With own Autoformat.

Colorize rows as you want in range 5x5 cells. Select it. Choose Format-Autoformat. Press Add and set name. Then select range for formating and apply this autoformat.

[holy-harp]

“Colorize Rows” means to manually add the background color you want. Do not use ISODD(ROW()) conditional format on your sample format cells.

[estes53]

This worked fine for me except only in empty cells.
Cells with data still have a white background.
Can’t seem to find what is overriding it.
Any suggestions?
Thanx, Alan

[Jim7fl1]

For this I generally have a sheet with named variables and other setup info
which has a cell with the background I want and it is named, for instance,
_blue

Then in Menu>Format>Conditional Formatting>Condition> click the “Cell value is” button and
select “Formula is” put the following formula into the form: MOD(ROW()+1,2=1

In Apply Style select your named style

Click OK

Done

You can apply this to the whole sheet or any portion.

I like my financial sheets to have the first 4 or 5 rows as plain white for headings and such, but I like the rest looking like the old fashioned dot matrix continuous paper with the alternating light and dark backgrounds.

Hope this helps someone.

Best Regards,

Jim Smith

[snison]

Hi:

When I use this and other methods found, specifically using ISEVEN(ROW()), it takes away the “center” formatting of the last column (F). When I try to correct this, it only changes the formatting for the even columns.

If I try to justify column C it also only does it for every even column.

How can I color alternate rows but keep formatting per column?

Thanks!

[JohnSUN]

OK. Just select range and run this macro:

Sub ColorizeTable
Dim oCurrentSelection As Variant
Dim oRows As Variant
Const nCellBackColor = 15132415 REM # "Blue gray"
Dim i As Long
	oCurrentSelection = ThisComponent.getCurrentSelection()
	If oCurrentSelection.supportsService("com.sun.star.table.CellRange") Then
		oRows = oCurrentSelection.getRows()
		For i = 0 To oRows.getCount()-1 Step 2
			oRows.getByIndex(i).setPropertyValue("CellBackColor", nCellBackColor)
		Next i
	EndIf
End Sub

[Lua]

@johnsun this works, but it colors entire row instead of selected one. The code below works for the latter purpose, please let me know if there is a better way to do it.

Sub ColorizeTable_2()
	Dim nCol As Long 'Column index variable
	Dim nRow As Long 'Row index variable
	Dim oCols	'Columns in the selected range
	Dim oRows	'Rows in the selected range
	Dim oRange
	Const nCellBackColor = 15132415 REM # "Blue gray"
	oRange = ThisComponent.getCurrentSelection()
	oCols = oRange.Columns : oRows = oRange.Rows
	For nCol = 0 To oCols.getCount() - 1
		For nRow = 0 To oRows.getCount() - 1 Step 2
			oRange.getCellByPosition(nCol, nRow).setPropertyValue("CellBackColor", nCellBackColor)
		Next
	Next
End Sub

[kola]

Hi,

Why is this feature so difficult??

Im not trolling, im frustrated. I am a simple guy who is a brick layer in real life. I don’t want to be a computer tech! All as I am trying to do is help my daughter young with her homework.

Not only do I have no clue to all that code I really don’t want to know or care.

I simply want one line white and another green or whatever so we can see the lines with ease in table view. Like it is in Excl.

This is like word requiring a computing degree to change the font?? I guess its something else I will have to just do without. Surely the ability to read the lines and lines of data without a physical ruler deserves its own button? Or do you lot just like typing in loads of code because you can?

As said not meaning to be rude.

[estes53]

Because it is free.
If you want all the neat features included pay for excel.

[jelabarre59]

Well, THAT was an obnoxious answer. The thing is, I remember LibreOffice used to have the bog-standard alternating bars as a built-in AutoFormat. Really can’t understand why it was taken out. Doesn’t matter if it’s a “free” offering or some overpriced clunker like Excel; the format used to be there, now it isn’t.

That said, it would be handy to have AutoFormat templates as installable templates. That was what I had been searching for (and don’t seem to exist).

[JohnSUN]

@jelabarre59 Sorry, I didn’t understand your comment. Didn’t you find this command in the menu?

[Edward3]

The AutoFormat feature allows you to select “more” options. Unchecking Font and Alignment should allow you to apply the autoformat while retaining the current font and alignment.