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

[lo_beginner]

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

[JohnSUN]

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

[lo_beginner]

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

[JohnSUN]

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.

[arindam]

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

[lo_beginner]

thanks, that works.

[ovi.sergiu]

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.

[arindam]

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.

[jggates]

How would you change the color from black.

Is it possible to change the color of an image rather than the cell?

[jggates]

@arindam I forgot to @ you in my previous post

[arindam]

You can use the code above to assign any colour you want by providing RGB values. For example, 255,255,255 is white and 0,0,0 is black. (check here)

And the changing colour of an image means? What you are trying to do?

[jggates]

@arindam I am trying to make an image change colors based off the data that is put in. I’m taking a map of Louisiana counties and based off their crime rate I need the image of the counties to change colors. The image starts off as white but for ex. if one county has a high murder rate it will turn red as another country with a low murder rate will turn green.

But I have still yet to even be able to get the image to change colors using macros.

[arindam]

Did you insert the image in Calc? What is their type (like jpg, png…) ? Can you share a screen shot

[jggates]

it is a png @arindam.

it says that the screenshot file is to big

[Boo2u2]

How does one adapt this to work on multiple rows (or columns) of rgb values outputting to the same number rows (or columns) of colors instead of just a single cell? For example my “r” ,“g”, “b” values are located in columns B, C, D respectively, and I have 15 rows in each column (rows 7 to 22). How do I set this up to change background colors of the corresponding rows in column E?

[jggates]

@arindam Sorry to bother you again but did you happen to figure out how to change the color of a picture using macros.

[arindam]

I guess its solved via another thread which you opened.