Ask Your Question
0

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

asked 2013-08-04 06:24:16 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-05 23:04:54.919288

Comments

There is now a similar question at https://superuser.com/questions/12664....

Jim K gravatar imageJim K ( 2017-11-08 20:51:39 +0200 )edit
1

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.

Lupp gravatar imageLupp ( 2017-11-09 17:38:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2013-08-06 11:02:31 +0200

JohnSUN gravatar image

updated 2017-11-09 13:21:35 +0200

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

edit flag offensive delete link more

Comments

Thanks for your help

Jamie gravatar imageJamie ( 2013-08-07 06:16:13 +0200 )edit

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

Jim K gravatar imageJim K ( 2017-11-08 20:50:11 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2017-11-09 13:24:10 +0200 )edit

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

Jim K gravatar imageJim K ( 2017-11-09 20:57:08 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-08-04 06:24:16 +0200

Seen: 2,962 times

Last updated: Nov 09 '17