sort by color

[spider]

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.

[tohuwawohu]

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?

[w_whalley]

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

[camel1cz]

Same problem as @Nissim. To solve it replace first line:

s=thiscomponent.getcurrentcontroller.sheets.getbyname("Sheet1")

With:

s=ThisComponent.sheets.getbyname("Sheet1")

[rautamiekka]

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

[Nissim]

I got this error:

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

[Funkyecat]

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.

[armando889]

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 …

[JamesGanong]

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

[libreuser01]

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