.Interior.ColorIndex

[ausch]

Hello,

I am using Excel2000 on a windowsXP machine. I have the following function.

Public Function testing(dataRange As Range)
Dim cell As Range

For Each cell In dataRange
cell.Interior.ColorIndex = 8
Next cell
End Function

The function works fine when it is used with the 'call' statement within a macro ie like the test subroutine below.

Sub test()
Call testing(ActiveSheet.Range("k1:k10"))
End Sub

When I call the function from a cell in excel eg in cell a1 I have the formula "=testing("k1:k10")". The cells are not colored.

Has somebody else found this problem?

thanks in advance

ausch

 

[obiron]

UDF functions cannot change formatting, they only return a value that can be stored in .Value, .Value2, .Text or .FormulaR1C1

To achieve what you are trying to do, you would need to put the range address in a cell and then call a macro that referenced that cell.

Your function should be a procedure.

 

[ausch]

Ahh that explains it. Thanks for your help.