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.