Reference Cell in custom format????

[lil_ern63]

I am trying to reference another cell in the custom format area. how it
is seet up currently, I have a column (A) that will have the quantity,
column (B) has the unit of the quantity, and column (C) has the unit
price, followed by column (D) that will have the total cost of the
object (i.e. column A * C = D)

I am trying to have column C set up so all I have to do is type the
unit price but the custom format of the cell will change the value to
include the label that is in the column next to it (B).......currently
for examples sake, I am trying to set the unit price to 50, and the
unit of the quantity will be tons. ideally the result after simply
imputting 50 into the cell it will display $50/ton.

I could get it to display this simply for this case, by changing the
custom format of the cell, but since the units will change from ton to
sf. or lf....etc I am looking for a more permanent
solution.......Please help!

 

[Dave Peterson]

I don't think you'll get your custom formatting to work that way.

But you could use an event macro that looks for changes in column B and formats
column D accordingly:

If you want to try, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim myStr As String

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("b:b"), Me.UsedRange)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub
Else
For Each myCell In myRng.Cells
If myCell.Value = "" Then
myStr = "General"
Else
myStr = "$0.00""/" & myCell.Value & """"
End If
myCell.Offset(0, 2).NumberFormat = myStr
Next myCell
End If

End Sub

And then back to excel to test it out.

 

[lil_ern63]

I appreciate the comment, but I am still unable to get output of the
cell to work, once again thank you for the try however.

 

[Dave Peterson]

What did you try?

I appreciate the comment, but I am still unable to get output of the
cell to work, once again thank you for the try however.