3

I have a user defined function in Excel. It is called as a formula function from spreadsheet cells and works fine.

I'd like the function to be able to change the cell's color depending on the value that it returns. Essentially, changing the cell's color is a side effect of the function.

I tried

Application.ThisCell.Interior.ColorIndex = 2

But it fails.

Larry K
  • 47,808
  • 15
  • 87
  • 140

6 Answers6

12

Here's a demonstration of how a VBA UDF can change the colouring of a sheets contents rather than using conditional formatting.

As long as both sheets have rows and columns sorted in the same order then this will compare for differences in every cell between two seperate Excel sheets.

You can add this into as many cells as you need to on a third sheet to detect differences between the same two cells on the two sheets with data on: =DifferenceTest(Sheet1!A1,Sheet2!A1)

And the function to be stored in the VBA editor as follows:

Function DifferenceTest(str1 As String, str2 As String) As String

    If str1 = str2 Then
            Application.Caller.Font.ColorIndex = 2
    Else
            Application.Caller.Font.ColorIndex = 3
            DifferenceTest = str1 & " vs " & str2
    End If

End Function
JonoMac
  • 293
  • 3
  • 6
9

This cannot be done. User defined functions cannot change the state of the workbook/worksheet etc.

Use Conditional Formatting to achieve what you are trying.

EDIT: This is more of a suggestion, not a real answer.

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • 2
    +1: More accurately, this can be done but absolutely shouldn't be done. Conditional formatting is almost definitely the way to go in this circumstance. – Daniel Dec 04 '12 at 15:12
  • @DanielCook: Can this be done in UDF?. If so, how? Thanks for +1. – shahkalpesh Dec 04 '12 at 15:19
  • 1
    @shahkalpesh it can be done with a UDF as per http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change. While it is complex I wouldn't be as "absolute" as Daniel, for this case it is overkill, there may be other cases where it is of use – brettdj Dec 07 '12 at 02:00
  • 1
    For reference, to help prevent a "deep rabbit hole", here's a possibly cleaner version from what @brettdj posted: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet – Selkie Jul 24 '18 at 21:52
1

No, you cannot alter a cell's color using a Function(). You can, however, alter it in a Sub() routine.

Simply write a Sub() that will run your function on the cells you wish it to be run on, then after each is run, put an If-statement to see if you want to color it based on the value it returns.

Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
1

You could create a vba code that runs automatically after there is a change in your sheet. Instead of hving the code in a seperate module you have to embed it in the sheet itself.

Right click on the sheet tab, choose View Code, and create the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Range("A1:B8") 'change cell range as needed

Select Case cell.Value
Case 8
cell.Interior.ColorIndex = 4 'cell color becomes green when cell value is 8
Case ""
cell.Interior.ColorIndex = 1 'cell color becomes black when cell is empty
Case Is < 6
cell.Interior.ColorIndex = 7 'cell color becomes pink when cell value is smaller than 6
Case Else
cell.Interior.ColorIndex = 0 'all other cells get no color
End Select

Next cell

End Sub
Diederik
  • 11
  • 1
0
Function HexToLongRGB(sHexVal As String) As Long
    Dim lRed As Long
    Dim lGreen As Long
    Dim lBlue As Long
    lRed = CLng("&H" & Left$(sHexVal, 2))
    lGreen = CLng("&H" & Mid$(sHexVal, 3, 2))
    lBlue = CLng("&H" & Right$(sHexVal, 2))
    HexToLongRGB = RGB(lRed, lGreen, lBlue)
End Function

Function setBgColor(ByVal stringHex As String)
    Evaluate "setColor(" & Application.Caller.Offset(0, 0).Address(False, False) & ",""" & stringHex & """)"
    setBgColor = ""
End Function

Sub setColor(vCell As Range, vHex As String)
    vCell.Interior.Color = HexToLongRGB(vHex)
End Sub
Nathan Tuggy
  • 2,237
  • 27
  • 30
  • 38
Đinh Quốc Tuấn
  • 11
  • 1
0

I tried the Evaluate method, which worked but immediately crashed (2007). The help mentions caching the address, so that's my approach - store the cell and color in a collection, then change the color after the calculation.

Dim colorCells As New Collection

Function UDF...
    UDF = <whatever>
    color = <color for whatever>
    colorCells.Add (Application.Caller)
    colorCells.Add (color)
End Function

Sub SetColor()
    While colorCells.Count <> 0
        colorCells(1).Interior.Color = colorCells(2)
        colorCells.Remove (1)
        colorCells.Remove (1)
    Wend
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    SetColor
End Sub
adoxa
  • 1
  • 1