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:
After replacing that, I always get
-1 as the number, regardless of bg color.
I got this error:
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: 220.127.116.11 of localc on linux.
the line that reads
failed for me,
the error that popped up was:
BASIC runtime error.
An exception occurred
But i was able get it to work by using this:
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 ?