Adding color to alternate rows of a table/spreadsheet
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.
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
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!
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.
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
@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
Asked: 2012-12-19 20:31:30 +0100
Seen: 65,363 times
Last updated: Nov 12 '13
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.