# sort by color [closed]

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.

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2020-08-21 12:09:18.240358

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?

( 2012-08-03 17:41:02 +0200 )edit

Sort by » oldest newest most voted

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

more

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I got this error:

more

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.

more

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")

more

After replacing that, I always get -1 as the number, regardless of bg color.

( 2016-10-22 01:05:22 +0200 )edit

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 ...

more

I am using Version: 5.3.3.2 of localc on linux.

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

( 2017-06-23 23:55:30 +0200 )edit

I try to do as the above comment but cannot, Could you please do it by youtube, if possible ?

more