calc function to change the cell background color to an rgb value based on 3 cells : solved


I have been struggling with trying to write this (my first lo function),
so please be understanding of my inexperience.

The function should change the background color to an rgb value based on 3 cells.
So something like bgcolor(r,g,b) where r, g and b are either values or better cell references.
e.g. if I enter ‘=bgcolor(c5,d5,e5)’ into cell f5 it should change the background color of cell f5.

function BGCOLOR(red, green, blue)
    CellBackColor = RGB(red, green, blue)
end function

I know this is not correct and I have tried multiple things, but rather than including
more wrong code here would really appreciate if someone could help me out.

working solution, although I have hard coded the ranges.
Many thanks to the two people below who helped me out.

Sub bgcolor_range
    Dim oSheet As Object
    oSheet = ThisComponent.CurrentController.ActiveSheet
    Dim oCell As Object
    Dim row As Integer, col As Integer
    for row = 5 To 158
        for col = 28 To 28
	            oCell = oSheet.getCellByPosition( col, row )
	            rrr = oSheet.getCellByPosition( col-3, row ).Value
		        ggg = oSheet.getCellByPosition( col-2, row ).Value
	    	    bbb = oSheet.getCellByPosition( col-1, row ).Value
                If oCell.String <> "" Then oCell.CellBackColor = RGB(rrr,ggg,bbb)
        next col
    next row
End Sub

Unfortunately, you cannot do this with a user-defined function (UDF). The fact is that Calc locked the sheet from any changes while calculating the formulas of this sheet.

But you have alternatives.

For example, you can write the code that colors the cells in the form of a subroutine (not a function) and execute it manually or by the event of the sheet “Content changed”

Or, if there are not too many variants of colors, you can create the desired cell styles in advance, with the right colors and names containing the RGB values. In this case, you can use the amazing function STYLE(), an analog of which is not present in other spreadsheets. (I’m not talking about Conditional Formatting - most likely its use in your case will be too time-consuming.)

thanks for your answer. Yes I think there are too many potential rgb combinations for the use of STYLE.
I have set up the macro to run on sheet content changed (which I found easy to do by right clicking on the sheet tab).

I will now try to write a subroutine and see how I get on with that…

Sub bgcolor(red As Integer, green As Integer, blue As Integer)
    CellBackColor = RGB(red, green, blue)
End Sub

No, not exactly. This code will not work. In the procedure, you will have to get each of the cells, take their values, calculate the color. Then take the target cell and change its background.

Try this:
Tested in LO 6.x.
Put your RGB values 0-255 - in A1, A2 and A3 cells respectively.

Dim my_cell as object

my_cell = ThisComponent.Sheets(0).getCellByPosition(2,0) 'C1 - target cell whose color would be changed
r1 = ThisComponent.Sheets(0).getCellByPosition(0,0).Value 'A1 - enter any number
g1 = ThisComponent.Sheets(0).getCellByPosition(0,1).Value 'A2 - do
b1 = ThisComponent.Sheets(0).getCellByPosition(0,2).Value 'A3 - do
my_cell.CellBackColor = rgb(r1,g1,b1)

End Sub

thanks, that works.

I want to put @arindam code, but I do not know where.
I have LibreOffice 6.2. Could someone write me the steps more explicit, please?
Thank you.

Follow the steps on where to put the code here: Writing a Macro in LibreOffice Calc - Getting Started

a) Go to the option from menu: Tools ==> Macros ==> Organize Macros ==> LibreOffice Basic. Below ‘LibreOffice basic macros’ window will open.

b) Give your desired name in macro name box and click New

c) Once you have clicked the New button, below macro editor will open. This is the place where you should be writing your code, debugging your code etc. You can see the name of the macro became the function name of your basic macro.