Hi! If the background color of a cell in calc is changed the value of CELL_BACKCOLOR is unaltered. Is there a way around this?

[SpenceOz]

I have LibreOffice 6.2.6.2, MS Edge, Windows 10, HP laptop [A6].

I use cell background colours to highlight different types of journal entries in a calc spreadsheet.

I use colour values from CELL_BACKCOLOR(SHEET();ROW();COLUMN()) in functions SUMIF and COUNTIF.

Which is OK if I don’t change the colour scheme or correct a colour because the colour values don’t update.

If I delete the original CELL_BACKCOLOR reference and retype it then it picks up the new value correctly.
But I don’t want to do that through the whole journal.

[anon73440385]

Have you tried either

1. Data -> Calculate -> Recalculate (F9) – or
2. Date -> Calculate -> Recalculate Hard (CTRL+SHIFT+F9)

?

BTW: CELL_BACKCOLOR seems to be user-defined function (macro), since there is no calc default function with this name

[SpenceOz]

The suggestions by Opaque work brilliantly.

The command data –> calculate can be used to reset the formula and return the correct value for the new colour—

• *data –> calculate –> recalculate* will work for a single cell or just the first cell in a selection
• *data –> calculate –> recalculate hard* will work for all the cells in a document
(Function key F9 does nothing on my laptop )

In this OOo forum thread https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=2762, there is a macro (villeroy 2008)
that creates some user-defined functions, among them CELL_BACKCOLOR.