Text Function - Custom Format ( Re-posted from Misc still looking for answer )
 

On Mon, 28 Jul 2003 22:57:55 GMT, "Gav !!" wrote:

Folks

I posted this in Misc - had a response thanks Richard - but it hasn't really answered my question - could Tom or John or Chip or Deb or Myrna or one of you many other gurus give me an idea if I am throwing good after bad or is this possible.


Is it possible to do a custom number format into the text function which
will allow me to colour the text similar to conditional formatting. The
problem i have is in one of the reports I have the staff have used one cell
to try and put 2 entries in with a slash in between. Say this is a target
value / agreed value and then at the end of the month I will get an actual
value in another cell which I will want to compare against each and give a
percentage increase or decrease. I have managed to write a formula to
seperate the values and give a percentage back using the text function but
now I want to highlight the increase ( good as green ) and decrease ( bad as
red ) within the formula if I can. Is this possible eg..


this is in cell E51

21,571 / 21,334

this is the actual in F51
20,462


The Formula I have is
=TEXT((TRIM(LEFT(E51,FIND("/",E51,1)-1))-F51)/(TRIM(LEFT(E51,FIND("/",E51,1)
-1)))%,"00")&"%"&" /
"&TEXT((TRIM(RIGHT(E51,FIND("/",E51,1)-1))-F51)/(TRIM(RIGHT(E51,FIND("/",E51
,1)-1)))%,"00")&"%"

What I would like is instead of "00" as the format to now apply the rules I
mentioned earlier as a custom format so that if there is an increase or
decrease the format will be acknowledged sort of like [Red}-00

Is this possible or can anyone suggest a better way of doing this without
increasing number of cells ???

Thanks in advance

Gav !!

The following event macro should do what you describe.

As written, it assumes that the target / agree info is in column E; the actual
is in the next column (column F) and the answer should go in column G.

It test cells E50:E55 to see if there is target/agree data, and then does the
rest.

The result is formatted as you described (.05% would show as 00%). This could
be changed but you'll also need to change the number of cells that the
Characters method returns if you do so.

To enter this, right click on your sheet tab; select View Code; and paste the
below macro into the window that opens.

Then adjust the line: AOI = [E50:E55] to reflect the range in which your
target/agree data is located. (As written, this needs to be in a single
column.

================================
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim Targ As Double, Agree As Double, Actual As Double
Dim TargPerCent As Double, AgreePerCent As Double
Dim Slash As Integer
Dim AOI As Range, c As Range

Set AOI = [E50:E55] 'Set this to range of target / agreed

For Each c In AOI
Slash = InStr(c.Value, "/")
If Slash 0 Then
Targ = CDbl(Left(c.Value, Slash - 1))
Agree = CDbl(Right(c.Value, Len(c.Value) - Slash))
Actual = c.Offset(0, 1).Value
TargPerCent = (Targ - Actual) / Targ
AgreePerCent = (Agree - Actual) / Agree

With c.Offset(0, 2)
.Value = Format(TargPerCent, "00%") & " / " _
& Format(AgreePerCent, "00%")
If TargPerCent 0 Then
.Characters(1, 3).Font.Color = vbGreen
Else
.Characters(1, 4).Font.Color = vbRed
End If
Slash = InStr(c.Offset(0, 2).Value, "/")
If AgreePerCent 0 Then
.Characters(Slash + 2, 3).Font.Color = vbGreen
Else
.Characters(Slash + 2, 4).Font.Color = vbRed
End If
.Characters(Slash, 1).Font.Color = vbBlack 'probably unnecessary
End With
End If
Next c
Application.EnableEvents = True
End Sub
===========================


--ron