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

Hi,

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

edit retag close merge delete

Sort by » oldest newest most voted

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.)

more

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

( 2018-08-24 10:10:13 +0100 )edit

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.

( 2018-08-24 11:00:30 +0100 )edit

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

more