How to replace styles of an Excel sheet cell with no style and then add value to cell?

[verbatim]

I have inherited an Excel sheet (from a previous employee) that uses background color to convey information, rather than placing values in the cells.

Although the document is an Excel sheet, our office is using LibreOffice for our suite.

Currently, this Excel sheet is looked at by human eyes only, but soon this sheet will be fed into a larger system. It will make more sense for this particular column in the sheet to have actual data rather than just styles.

How can I search/replace all cells in a column of data based on styling of the cell, AND add a value in the place of the background color.

1. if the cell has a background color (in this case: black), replace that with no background color.
2. then, add some value into the cell (for this sheet, a simple TRUE or FALSE should probably be good enough)

Very rough pseudo-code for what I hope to be able to do:

for each ( cell in column-X of sheet(1) ) {
    if cell.background.color == BLACK {
        cell.background.color = NO_BACKGROUND_COLOR
        cell.value = "false"
    }
}

[Zizi64]

First suggestion: Save the file into the native, international standard ODF file format.

You can eliminate the formatting properties by selecting the relevant cell range and then hit Ctrl-M.
Do it after you put the values into the cells based on the colors (manually or by macros).

Second suggestion: Use Cell Styles instead of the direct (manual) formatting method.

A cell has more than 1,000,000 rows. It better to examine (by macro) a selected cell range instead of all of the rows.

Here is a sample file with an embedded macro:
Color to values.ods

[verbatim]

Thank you. This is doing the trick.

Also, I tried to give you an upvote, but am unable to, as I am new around here. Got the message: “>5 points required to upvote”. So I did check the checkmark, to signal that this is the answer I needed.

Thanks again.