Hello. I’m having a problem with a spreadsheet that has almost 5,000 rows and 7 columns, some of which are not adjacent. The problem is as follows: My goal is to colorize a list of cells that I’ve been given.
To do this, I’ve been given the cell address, not the text or number they want to search for within the cell. The only way I’ve found to select cells is to enter the cell address in the name box and then colorize them. However, the list is almost 1,500 cells long. The name box only allows you to enter a single individual cell or ranges of cells, but not a list of cells. I found on a website that it could be done using the ~ operator, but I tried that, and it didn’t work. I’ve also tried the multiple selection options (Extending Selection and Adding Selection) at the bottom of the LibreOffice Calc status bar, and they don’t work either. It also doesn’t solve the problem because I’d have to go to each cell individually. I’ve also read information about functions like address, indirect, and offset, but they don’t work for this purpose. The ideal approach would be to put the list of cell addresses in find and replace using regular expressions, but that’s not possible either, or to use a formula in a function that I don’t know which one to use.
My LibreOffice version is 7.2.7.2
The problem is easy to solve: coloring a list of cells. But since they haven’t given me what to search for (text or numbers) and I can use functions like find, search, find, and replace, tools I usually use, but instead just give me the cell addresses, it’s very difficult to solve.
I hope the community can help me if possible.
Regards
maybe you can adapt something like → Need a MACRO in CALC - apply background color to cell for whole column
Create a new style with the appropriate background color. Download the “COUNTSTYLE” extension to your computer. This allows you to select cells with this property.
This wasn’t what the questioner wanted to achieve.
Very strange idea.
However, see attachment:
disdask_128168_selectListedCells.ods (186.9 KB)
It contains a complete example with random data (only 4 columns).
That’s impressive. I hadn’t thought of using TextJoin. To join cell addresses, I had thought of the Concat function. Thanks for your help. I’m going to have to learn how to program and create macros. I tried it once and found it very difficult, but I promise to try again. Thanks again.
Better use it with second parameter 1 or TRUE (skip empty) in such a case.