sort by color [closed]
Some of my cells have background color and I need to be able to sort by color, but cannot seem to find how to do that. All help appreciated.
First time here? Check out the FAQ!
asked 2012-08-03 05:11:37 +0200
This post is a wiki. Anyone with karma >75 is welcome to improve it.
Some of my cells have background color and I need to be able to sort by color, but cannot seem to find how to do that. All help appreciated.
I try to do as the above comment but cannot, Could you please do it by youtube, if possible ?
You can try with something like this...
function bgcolor()
total=2556
for I=1 To total
s=ThisComponent.sheets.getbyname("Sheet1")
cell=s.getCellRangeByName("A"&I)
bgcolor=cell.cellBackColor
dest=s.getCellRangeByName("O"&I)
dest.String=bgcolor
next I
end function
Where total is the length of the column A..
The column A is the origin and O is the destination column ...
Sheet1 is the name of the "Working sheet"
I know it's the simplest code of the world,but it works ...
I am using Version: 5.3.3.2 of localc on linux.
the line that reads
s=ThisComponent.sheets.getbyname("Sheet1")
failed for me,
the error that popped up was:
BASIC runtime error. An exception occurred Type: com.sun.star.container.NoSuchElementException Message: .
But i was able get it to work by using this:
s=ThisComponent.Sheets(0)
I am guessing that if wanted Sheet2 you could use (1)
To enter this macro I did Tools>Macros>Organize Macros>LibreOffice Basic
answered 2013-03-26 10:48:45 +0200
This post is a wiki. Anyone with karma >75 is welcome to improve it.
Same problem as @Nissim. To solve it replace first line:
s=thiscomponent.getcurrentcontroller.sheets.getbyname("Sheet1")
With:
s=ThisComponent.sheets.getbyname("Sheet1")
After replacing that, I always get -1
as the number, regardless of bg color.
answered 2013-03-08 22:45:45 +0200
This post is a wiki. Anyone with karma >75 is welcome to improve it.
Instructions poor. Do you enter '=BGCOLOR(CELL("COL",A1),CELL("ROW",A1)) where A1 is the target cell." in another column and drag down
where do you enter the macro.
answered 2012-10-04 03:06:25 +0200
This post is a wiki. Anyone with karma >75 is welcome to improve it.
I got this error:
LibreOffice 3.5 BASIC runtime error: property or method not found: sheets.
answered 2012-08-03 19:44:56 +0200
This post is a wiki. Anyone with karma >75 is welcome to improve it.
Here's one way. Add a macro function that returns a cell's background color as a number. Add a new column filled with that function that addresses the target background-colored cells. Sort on the numbers in the new column (use Ctrl-Shift-F9 to recalculate all functions before sorting. Be sure to include Option/Formats in the sort)
The auxilliary column contains the formula =BGCOLOR(CELL("COL",A1),CELL("ROW",A1)) where A1 is the target cell.
function bgcolor(col,row)
rem # Returns background color of addressed cell
s=thiscomponent.getcurrentcontroller.sheets.getbyname("Sheet1")
cell=s.getcellbyposition(col-1,row-1)
bgcolor=cell.cellBackColor
end function
Asked: 2012-08-03 05:11:37 +0200
Seen: 22,027 times
Last updated: May 13 '15
Is there a way to colour a series of cells with shaded tones? [closed]
Is there an automatic sort function in Calc? [closed]
Change XY chart data colors [closed]
How do I export a chart in an image format from LibreOffice Calc? [closed]
Are there plans for a "papercut" project for libreoffice [closed]
Is it normal for Calc goal seek to take very long? [closed]
Please refine "Search" in Calc - implement functions in Gnumeric [closed]
LibreOffice Calc will not link to external data via internet [closed]
Is there any sort of dependency between cell background color and cell content (value)? Or, to put the question in another way: how is the background color determined?