User Defined Function Working in Immediate window but nowhere else

[D_Miller]

Good Morning guys,

I have a User defined Function (UDF) which takes various ranges of cells in a column and compares them. Each range consists of one column only and the objective of the function is to look across these columns and identify the cell with the lowest value and change its color to yellow. The business context is that i can have between 2-5 suppliers with their prices and the aim is to identify the supplier with the lowest price on each item they have quoted on.

The function works fine but the problem is it only works when I call it from the immediate window. '?udf_IdentifyLowestCost("A2:A5", "B2:B5", "C2:C5", "D2:D5")

Outside the VBA editor and on the Excel Data entry area if I call the same UDF by using =udf_IdentifyLowestCost(A2:A5, B2:B5, C2:C5, D2:D5) nothing happens.

Can someone shed some light on this?

 

[Fluff]

You're welcome & thanks for the feedback

 

[D_Miller]

Ok guys I got a wonderful solution to my problem without adjusting my code.

I created a user form from the VBE environment and passed the data in the controls from this from into my UDF function then run it from the workbook.

Eureka, it causes my UDF function to format the workbook and change the formatting on the cells as per the code in the function.

Feeling so happy now...!