0

This short example function returns two values and I want to color one of the cells. Can anyone help me with a solution on how to do the coloring?

Function test() As Variant

    Dim x As Variant
    ReDim x(1 To 1, 1 To 2)
    
    x(1, 1) = 1
    x(1, 2) = 2
    
    'ActiveCell.Interior.ColorIndex = 3
    ActiveCell.Offset(0, 1).Interior.ColorIndex = 3
    
    test = x

End Function
Tron36
  • 1
  • `Application.ThisCell` Or `Application.Caller` will give you the cell with the formula. – Tim Williams Aug 28 '22 at 16:32
  • You can't set a range's fill color from a UDF (see https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1) There is a work-around though: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet – Tim Williams Aug 29 '22 at 18:40

1 Answers1

0

This would color the selected cell yellow, if you want a different color the easiest way is to record yourself changing a cell to that color.

With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Stuart
  • 144
  • 5
  • This is ok, but it does not answer the question. This approach works fine when I'm using a sub routine, but it's not this straight forward within a function. – Tron36 Aug 28 '22 at 19:02