Ask Your Question

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

asked 2018-08-24 08:15:46 +0200

lo_beginner gravatar image

updated 2018-08-24 14:11:20 +0200


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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-08-24 09:54:16 +0200

JohnSUN gravatar image

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

edit flag offensive delete link 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
lo_beginner gravatar imagelo_beginner ( 2018-08-24 10:10:13 +0200 )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.

JohnSUN gravatar imageJohnSUN ( 2018-08-24 11:00:30 +0200 )edit

answered 2018-08-24 12:55:56 +0200

updated 2018-08-24 12:59:53 +0200

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
edit flag offensive delete link more


thanks, that works.

lo_beginner gravatar imagelo_beginner ( 2018-08-24 13:55:48 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-08-24 08:15:46 +0200

Seen: 433 times

Last updated: Aug 24 '18