Using LibreOffice Calc dynamically set the background color of a cell to the Hex value in that cell?

[Jamie1]

This is bugging me. I’m using LibreOffice Calc dynamically set the background color of a cell to the Hex value in that cell, but the kludge solution I have works on the wrong sheet;

function BGCOLOR(col, row, red, green, blue)
	Dim Doc As Object
	Dim Sheet As Object
	Dim Cell As Object   
	
	Doc = ThisComponent
	Sheet = Doc.getSheets().getByName("From_DakkaDakka")
rem	Sheet = Doc.getSheets().getByIndex(3)
rem	Sheet = ThisComponent.getCurrentController().getActiveSheet()
	
	Cell = Sheet.getCellByPosition(col - 1, row - 1)
	Cell.CellBackColor = RGB(red, green, blue)
	BGCOLOR=Cell.CellBackColor
end function

Then in the cell I have;

=BGCOLOR(CELL("COL",J2),CELL("ROW",J2),HEX2DEC(LEFT(J2,2)),HEX2DEC(MID(J2,3,2)),HEX2DEC(RIGHT(J2,2)))

But this is an ugly solution. Does anyone know a better way?

[jimk]

There is now a similar question at conditional formatting - LibreCalc: Change background color - Super User.

[Lupp]

Standard comment: You cannot set cell attributes by side-effects of a user function called for the evaluation of a formula in a cell of the same sheet. The restriction formerly also included all the other properties of cells, but was loosened a while ago to the effect that now the properties .String , .Value , .Formula are no longer locked. The last one rejects to be set to “”, however.
I do not know a specification about all this. Changes may occur without notice.

[JohnSUN]

May be so? Set the background color of a cell from the Hex value in other cell.ods

Update 2017-11-09 - file was deleted. And resored

[Jamie1]

Thanks for your help

[jimk]

@JohnSUN: This link is now broken. Do you remember what it contained, and if so, could you give a summary?

[JohnSUN]

No, I don’t remember, many years have passed. But I attach the file from the archive to the previous answer

[jimk]

Thank you; that is helpful. It uses basically the same concept as my answer from superuser.com linked above.