Ask Your Question

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?

asked 2019-08-26 16:54:32 +0200

SpenceOz gravatar image

I have LibreOffice, 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.

edit retag flag offensive close merge delete


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

Opaque gravatar imageOpaque ( 2019-08-26 17:04:48 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-08-29 08:02:54 +0200

SpenceOz gravatar image

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, there is a macro (villeroy 2008) that creates some user-defined functions, among them CELL_BACKCOLOR.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-26 16:54:32 +0200

Seen: 124 times

Last updated: Aug 29 '19