Hello,
here is my try at a solution as a macro function (see end of this post) which can be called via
=SETCOLOR($Sheet1.A3,"Sheet1","B3")
- the 1st argument is a reference to a cell containing a hex color value (e.g. “ff0000” without the quotes)
- the 2nd and 3rd argument are strings which specify the cell which shoult be colored
Sadly i found that this solution has one major drawback:
When a macro is called as a Calc
function, the macro cannot modify any
value in the sheet from which the
macro was called.
src.: Accessing cells directly - Apache OpenOffice Wiki
So to use this function, it has to be placed aka. called from a differnt sheet as from the one where you want to change the colors of the cells. If you do that, this works quite nicely.
Sheet2 where the function is called:
Sheet1 where BG color is set:
Here the macro function:
Function SETCOLOR(hexColor as String, dstSheet as String, dstCell)
dstSheet = Trim(dstSheet)
dstCell = Trim(dstCell)
Set oActSheet = ThisComponent.CurrentController.ActiveSheet
Dim actSheetName As String
actSheetName = oActSheet.Name
REM When a macro is called as a Calc function, the macro cannot modify any value in the sheet from which the macro was called.
REM https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Accessing_cells_directly
if ( dstSheet = actSheetName) then
SETCOLOR="ERR: macro function can not influence activ sheet"
Exit Function
endif
Set oDstSheet = ThisComponent.getSheets().getByName(dstSheet)
Set oDstCell = oDstSheet.getCellRangeByName(dstCell)
REM remove trailing spaces
hexColor = UCase(Trim(hexColor))
Dim decColor As Long
REM check length
if (Len(hexColor) <> 6) then
SETCOLOR = "ERR: invalid hex code, length <> 6"
Exit Function
endif
REM check chars
For i = 1 To Len(hexColor)
c = Mid(hexColor, i, 1)
Select Case c
Case "0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"
Case Else
SETCOLOR = "ERR: invalid hex code, invalid hex char"
Exit Function
End Select
Next
decColor = CLng("&H" & hexColor)
oDstCell.CellBackColor = decColor
SETCOLOR = dstSheet & ":" & dstCell & " set to " & hexColor
End Function
Maybe it still helps someone.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Have a nice day and let’s (continue to) “Be excellent to each other!”
