the following macro function (see end of this post) does what you want. Just put it into Tools → Macros → “Edit Macros” and you can call it from inside any cell via
- The 1st argument is the cell to check quoted as a string
- The 2nd argument is a hexadecimal color value quoted as a string To view or set the hex value of a cell open the color picker ref1. ref2..
- the 3rd argument is the result to set when the background color matches
- the 4th argument is the result to set when the background color deos not match
Here the macro function you have to import:
Function IFCOLOR(cellName As String, hexColor As String, ifValue as variant, elseValue as variant)
REM remove trailing spaces
cellName = Trim(cellName)
hexColor = UCase(Trim(hexColor))
Dim decColor As Long
REM check length
if (Len(hexColor) <> 6) then
SUMIFCOLOR = "ERR: invalid hex code, length <> 6"
REM check chars
For i = 1 To Len(hexColor)
c = Mid(hexColor, i, 1)
Select Case c
SUMIFCOLOR = "ERR: invalid hex code, invalid hex char"
REM convert to long
decColor = CLng("&H" & hexColor) REM convert to long
REM sum result
sum = 0.0
Set oSheet = ThisComponent.CurrentController.ActiveSheet
Set oCell = oSheet.getCellRangeByName(cellName)
if(oCell.CellBackColor = decColor) then
IFCOLOR = ifValue
IFCOLOR = elseValue
Hope that helps.
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!