I am attempting to use a user-defined function to work on a cell value and
change that cell value's format. IE if A1<20 format "0.00 uL" or if A12000
divide by 1000 then format "0.0 mL", and so on...
My problem is that I create a circular reference because the cell to change
is the cell the formula would be in.
I did have a multi-level if statement in A1, but was hoping that a function
in VBA would be easier.

Hi

Use an event code to change the format of the target cell. Change
TargetRange to refer to the range where you want to do this formatting.

As it's an event code it has to be placed in the code sheet for the desired
sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range
Set TargetRange = Range("A1:A10") '<=== change to suit

Set isect = Intersect(Target, TargetRange)

If Not isect Is Nothing Then
Application.EnableEvents = False
If Target.Value < 20 Then
Target.Value = Format(Target.Value, "0.00 uL")
ElseIf Target.Value 2000 Then
Target.Value = Target.Value / 1000
Target.Value = Format(Target.Value, "0.0 mL")
End If
Application.EnableEvents = True
End If
End Sub

Regards´,
Per

"df_newtovba" skrev i meddelelsen
...
I am attempting to use a user-defined function to work on a cell value and
change that cell value's format. IE if A1<20 format "0.00 uL" or if
A12000
divide by 1000 then format "0.0 mL", and so on...
My problem is that I create a circular reference because the cell to
change
is the cell the formula would be in.
I did have a multi-level if statement in A1, but was hoping that a
function
in VBA would be easier.

A function can not change the format of a cell. That is true for UDF's and
built in functons. Functions return values to the cells they are in. That
being said you could use a simple custom format something like this one...

[<20]0.0 ul;[2000] #.0," ml";General

Right click the cell and select Format Cell... - Number - Custom and add
that format.
--
HTH...

Jim Thomlinson


"df_newtovba" wrote:

I am attempting to use a user-defined function to work on a cell value and
change that cell value's format. IE if A1<20 format "0.00 uL" or if A12000
divide by 1000 then format "0.0 mL", and so on...
My problem is that I create a circular reference because the cell to change
is the cell the formula would be in.
I did have a multi-level if statement in A1, but was hoping that a function
in VBA would be easier.

Since it is what the user asked for this is not criticism, but if you divide
the number (2000) as opposed to just reformat it you can't add the column or
reasonably perform any calculations on the data set since this process
modifies the underlying values.
--
HTH...

Jim Thomlinson


"Per Jessen" wrote:

Hi

Use an event code to change the format of the target cell. Change
TargetRange to refer to the range where you want to do this formatting.

As it's an event code it has to be placed in the code sheet for the desired
sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range
Set TargetRange = Range("A1:A10") '<=== change to suit

Set isect = Intersect(Target, TargetRange)

If Not isect Is Nothing Then
Application.EnableEvents = False
If Target.Value < 20 Then
Target.Value = Format(Target.Value, "0.00 uL")
ElseIf Target.Value 2000 Then
Target.Value = Target.Value / 1000
Target.Value = Format(Target.Value, "0.0 mL")
End If
Application.EnableEvents = True
End If
End Sub

Regards´,
Per

"df_newtovba" skrev i meddelelsen
...
I am attempting to use a user-defined function to work on a cell value and
change that cell value's format. IE if A1<20 format "0.00 uL" or if
A12000
divide by 1000 then format "0.0 mL", and so on...
My problem is that I create a circular reference because the cell to
change
is the cell the formula would be in.
I did have a multi-level if statement in A1, but was hoping that a
function
in VBA would be easier.