Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email notification of answer posted | Excel Discussion (Misc queries) | |||
need second answer to question posted yesterday (niek Otten) | Excel Worksheet Functions | |||
Recognizing misc. text | Excel Discussion (Misc queries) | |||
Text Function - Custom Format ( Re-posted from Misc still lookingfor answer ) | Excel Programming | |||
Text Function - Custom Format ( Re-posted from Misc still looking for answer ) | Excel Programming |