Ask Your Question

In Calc, is there a way to dynamically fill a cell with a color based on a hex code/RGB values/LO color code in an adjacent cell?

asked 2018-12-29 20:06:58 +0100

spockface gravatar image

I'm trying to create a table of skin colors based on an art project, as part of a workbook meant to randomly generate NPCs for tabletop roleplaying games. I'm hoping there's some way to take either the hex code, RGB values, or LO color code (the value returned by COLOR[Rvalue,Gvalue,Bvalue,Alpha]) and automatically fill an adjacent cell with that color -- I have 200 hex codes in my table, which is a lot to do by hand. I'm familiar with formulas and conditional formatting, but I haven't been able to figure this one out.

Any help would be much appreciated!

edit retag flag offensive close merge delete


This is not difficult to implement - just create 200 cell styles and use the STYLE() function. Of course, I do not propose to do it manually - this is a boring job, not for an artist. The macro for creating all these styles is not complicated, I am ready to write and show it. But you must help me. Please edit your question and attach a file with samples of color codes that you are going to use. Even creating a test set of such values ​​is boring.

JohnSUN gravatar imageJohnSUN ( 2018-12-29 20:27:41 +0100 )edit

What do you mean by "LO color code"?

Lupp gravatar imageLupp ( 2018-12-30 16:31:05 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-12-30 00:14:45 +0100

Lupp gravatar image

updated 2018-12-30 14:00:06 +0100

You may also play with this attached spreadsheet document (rectified version). It demonstrates ways by direct formatting based on code. You can get an example cell for any RGB value by simply entering it into a cell set to the appropriate named CellStyle.
There is an additional demo using a function. Due to restrictions concerning the side-effects of user functions it can only be called from one sheet with the effect in another sheet.

===Edit1 2018-12-30 12:57 CET===
By accident I fortunately noticed that I had attached a not fully functional version of the demonstrating document. I replaced it with the correct version now.
In addition I now attach this extremely reduced demo to emphasise the principles of the solution using a SheetEvent. The SheetEvent 'Content changed' passes a parameter to the handler which is not specified to be of a specific type. It can be a single SheetCell, a single SheetCellRange or a collection of SheetCellRanges (3 different services to use!). A complete handler for the event should therefore contain proceedings for all the probable cases.

edit flag offensive delete link more

answered 2018-12-29 21:34:37 +0100

JohnSUN gravatar image

Well, you can try this macro:

Function crtStylesIfNeed(sColorCode As String, Optional newStylePrefix As String) As String
Dim oStyleFamilies As Variant, oCellStyles As Variant, oENames As Variant
Dim oBaseCellStyle As Variant, nameParentStyle As String,
Dim oNewCellStyle As Variant, nameNewStyle As String, nCellBackColor As Long
    If IsMissing(newStylePrefix) Then newStylePrefix = "c_"
    nameNewStyle = newStylePrefix & sColorCode
    crtStylesIfNeed = nameNewStyle

    oStyleFamilies = ThisComponent.getStyleFamilies()
    oCellStyles = oStyleFamilies.getByName("CellStyles")

    If oCellStyles.hasByName(nameNewStyle) Then Exit Function ' Present, nothing to do

    If oCellStyles.hasByName(newStylePrefix) Then
        oBaseCellStyle = oCellStyles.getByName(newStylePrefix)
        oBaseCellStyle = oCellStyles.getByIndex(0) ' "Default"
    nameParentStyle = oBaseCellStyle.getName()

    oNewCellStyle = ThisComponent.createInstance("")
    oNewCellStyle.ParentStyle = nameParentStyle
    oCellStyles.insertByName(nameNewStyle, oNewCellStyle)
    nCellBackColor = CLng(Replace(sColorCode,"#","&H"))
    oNewCellStyle.setPropertyValue("CellBackColor", nCellBackColor)
    oNewCellStyle.setPropertyValue("NumberFormat", oBaseCellStyle.NumberFormat)
End Function

Put the formula in the cell and get the result:


This is spreadsheet with demo of this macro - C:\fakepath\Skin colors.ods


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-29 20:06:58 +0100

Seen: 357 times

Last updated: Dec 30 '18