English
Ask Your Question
1

sort by color

asked 2012-08-03 05:11:37 +0200

this post is marked as community wiki

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

updated 2012-08-03 05:11:37 +0200

spider gravatar image

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 flag offensive close merge delete

Comments

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?

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

5 answers

Sort by » oldest newest most voted
2

answered 2012-08-03 19:44:56 +0200

this post is marked as community wiki

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

updated 2013-01-29 15:06:08 +0200

qubit gravatar image

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
edit flag offensive delete link more
1

answered 2013-03-26 10:48:45 +0200

this post is marked as community wiki

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

updated 2013-03-26 10:49:22 +0200

camel1cz gravatar image

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

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

With:

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

edit flag offensive delete link more
0

answered 2012-10-04 03:06:25 +0200

this post is marked as community wiki

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

updated 2012-10-04 03:06:25 +0200

Nissim gravatar image

I got this error:

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

edit flag offensive delete link more
0

answered 2014-01-31 02:14:07 +0200

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

edit flag offensive delete link more
0

answered 2013-03-08 22:45:45 +0200

this post is marked as community wiki

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

updated 2013-03-08 22:45:45 +0200

Funkyecat gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

Follow
1 follower

Stats

Asked: 2012-08-03 05:11:37 +0200

Seen: 2,183 times

Last updated: Jan 31