sort by color

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.

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?

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

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.

I got this error:

LibreOffice 3.5
BASIC runtime error:
property or method not found: sheets.

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.

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

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