Folks

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 !!

Just out of curiosity, is it really that important to keep those 2 values in
one cell? Your situation would be soooo much easier if you did that. As it
is, you have to go through all of this trouble to separate them, and then,
apparently, you want to perform some formatting.
Richard Choate

"Gav !!" <gavin.davidson@qr.com.au> wrote in message
news:rI%Ua.137$U74.6997@news.optus.net.au...
Folks

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 !!

Managers make things difficult - Then I have to do difficult things to make
their life easy

Actually it can't be changed because the report has already been approved by
senior executives and they like it, so now to stop incorrect figures being
calculated this is what I have to work with.

"Richard Choate" <rchoatecpa@NoSpam.com> wrote in message
news:e1RsSLMVDHA.3148@tk2msftngp13.phx.gbl...
> Just out of curiosity, is it really that important to keep those 2 values
in
> one cell? Your situation would be soooo much easier if you did that. As it
> is, you have to go through all of this trouble to separate them, and then,
> apparently, you want to perform some formatting.
> Richard Choate
>
> "Gav !!" <gavin.davidson@qr.com.au> wrote in message
> news:rI%Ua.137$U74.6997@news.optus.net.au...
> Folks
>
> 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 !!
>
>
>

Let me suggest the Ozgrid/Dave Hawley site because he has a lot of cell
formatting stuff which might help you. Here is the link:
http://www.ozgrid.com/
Richard Choate
"Gav !!" <gavin.davidson@qr.com.au> wrote in message
news:MM3Va.149$U74.8323@news.optus.net.au...
Managers make things difficult - Then I have to do difficult things to make
their life easy

Actually it can't be changed because the report has already been approved by
senior executives and they like it, so now to stop incorrect figures being
calculated this is what I have to work with.

"Richard Choate" <rchoatecpa@NoSpam.com> wrote in message
news:e1RsSLMVDHA.3148@tk2msftngp13.phx.gbl...
> Just out of curiosity, is it really that important to keep those 2 values
in
> one cell? Your situation would be soooo much easier if you did that. As it
> is, you have to go through all of this trouble to separate them, and then,
> apparently, you want to perform some formatting.
> Richard Choate
>
> "Gav !!" <gavin.davidson@qr.com.au> wrote in message
> news:rI%Ua.137$U74.6997@news.optus.net.au...
> Folks
>
> 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 !!
>
>
>

And one more bad thing:  If your cell is going to contain a formula, then you
can't use that character by character color formatting.  It's whole cell at a
time.

How about a macro that replaces the formulas.  This won't be dynamic which means
if you change the data, you'll have to rerun the macro.

If yes, then I guessed that you have the 3 cells in a row.  The first two
columns are like E51 and F51.  I put the output in G51 (so that it always looks
at the preceding two cells.

So select a bunch of cells and run this:

Option Explicit
Sub fixemUp()

    Dim tempVal0 As Double
    Dim tempVal1 As Double
    Dim tempVal2 As Double
    Dim LeftHandSide As String
    Dim RightHandSide As String
    
    Dim slashPos As Long
    Dim myCell As Range
    Dim myRng As Range
    
    Set myRng = Selection
    
    For Each myCell In myRng.Cells
        If myCell.Column < 3 Then
            'do nothing
        Else
            If IsNumeric(myCell.Offset(0, -1).Value) Then
                tempVal0 = myCell.Offset(0, -1).Value
                slashPos = InStr(1, myCell.Offset(0, -2).Value, " / ")
                If slashPos = 0 Then
                    'do nothing
                Else
                    tempVal1 = CLng(Left(myCell.Offset(0, -2).Value, _
                                       slashPos - 1))
                    tempVal2 = CLng(Right(myCell.Offset(0, -2).Value, _
                                       slashPos - 1))
                    LeftHandSide = Format(tempVal1 / tempVal0 - 1, "00%")
                    RightHandSide = Format(tempVal2 / tempVal0 - 1, "00%")
                    myCell.Value = LeftHandSide & " / " & RightHandSide
                    myCell.Font.ColorIndex = xlAutomatic
                    
                    If Left(LeftHandSide, 1) = "-" Then
                        myCell.Characters(1, Len(LeftHandSide) - 1) _
                                .Font.ColorIndex = 3
                    End If
                    If Left(RightHandSide, 1) = "-" Then
                        myCell.Characters(Len(myCell.Value) _
                              - Len(RightHandSide) + 1, _
                                      Len(RightHandSide) - 1) _
                                           .Font.ColorIndex = 3
                    End If
                End If
            End If
        End If
    Next myCell

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



"Gav !!" wrote:
> 
> Managers make things difficult - Then I have to do difficult things to make
> their life easy
> 
> Actually it can't be changed because the report has already been approved by
> senior executives and they like it, so now to stop incorrect figures being
> calculated this is what I have to work with.
> 
> "Richard Choate" <rchoatecpa@NoSpam.com> wrote in message
> news:e1RsSLMVDHA.3148@tk2msftngp13.phx.gbl...
> > Just out of curiosity, is it really that important to keep those 2 values
> in
> > one cell? Your situation would be soooo much easier if you did that. As it
> > is, you have to go through all of this trouble to separate them, and then,
> > apparently, you want to perform some formatting.
> > Richard Choate
> >
> > "Gav !!" <gavin.davidson@qr.com.au> wrote in message
> > news:rI%Ua.137$U74.6997@news.optus.net.au...
> > Folks
> >
> > 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 !!
> >
> >
> >

-- 

Dave Peterson
ec35720@msn.com

Dave I have tried to run the code but can't seem to get it to work, if
I use 3 columns say A,B & C and I put the following values in each

A1                B1              C1
1000/1400        1200            blank

then I highlight the 3 cells and run the macro - nothing happens

When I did a watch in the backend it says that

Set myRng = Selection
    
    For Each myCell In myRng.Cells
        If myCell.Column < 3 Then
            'do nothing

this part of the code only = 1 so it does nothing. Shouldn't it be
picking up that I have selected 3 columns.

Thanks in advance Gav !!


Dave Peterson <ec35720@msn.com> wrote in message news:<3F25AF13.48D794FB@msn.com>...
> And one more bad thing:  If your cell is going to contain a formula, then you
> can't use that character by character color formatting.  It's whole cell at a
> time.
> 
> How about a macro that replaces the formulas.  This won't be dynamic which means
> if you change the data, you'll have to rerun the macro.
> 
> If yes, then I guessed that you have the 3 cells in a row.  The first two
> columns are like E51 and F51.  I put the output in G51 (so that it always looks
> at the preceding two cells.
> 
> So select a bunch of cells and run this:
> 
> Option Explicit
> Sub fixemUp()
> 
>     Dim tempVal0 As Double
>     Dim tempVal1 As Double
>     Dim tempVal2 As Double
>     Dim LeftHandSide As String
>     Dim RightHandSide As String
>     
>     Dim slashPos As Long
>     Dim myCell As Range
>     Dim myRng As Range
>     
>     Set myRng = Selection
>     
>     For Each myCell In myRng.Cells
>         If myCell.Column < 3 Then
>             'do nothing
>         Else
>             If IsNumeric(myCell.Offset(0, -1).Value) Then
>                 tempVal0 = myCell.Offset(0, -1).Value
>                 slashPos = InStr(1, myCell.Offset(0, -2).Value, " / ")
>                 If slashPos = 0 Then
>                     'do nothing
>                 Else
>                     tempVal1 = CLng(Left(myCell.Offset(0, -2).Value, _
>                                        slashPos - 1))
>                     tempVal2 = CLng(Right(myCell.Offset(0, -2).Value, _
>                                        slashPos - 1))
>                     LeftHandSide = Format(tempVal1 / tempVal0 - 1, "00%")
>                     RightHandSide = Format(tempVal2 / tempVal0 - 1, "00%")
>                     myCell.Value = LeftHandSide & " / " & RightHandSide
>                     myCell.Font.ColorIndex = xlAutomatic
>                     
>                     If Left(LeftHandSide, 1) = "-" Then
>                         myCell.Characters(1, Len(LeftHandSide) - 1) _
>                                 .Font.ColorIndex = 3
>                     End If
>                     If Left(RightHandSide, 1) = "-" Then
>                         myCell.Characters(Len(myCell.Value) _
>                               - Len(RightHandSide) + 1, _
>                                       Len(RightHandSide) - 1) _
>                                            .Font.ColorIndex = 3
>                     End If
>                 End If
>             End If
>         End If
>     Next myCell
> 
> End Sub
> 
> 
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> 
> 
> 
> "Gav !!" wrote:
> > 
> > Managers make things difficult - Then I have to do difficult things to make
> > their life easy
> > 
> > Actually it can't be changed because the report has already been approved by
> > senior executives and they like it, so now to stop incorrect figures being
> > calculated this is what I have to work with.
> > 
> > "Richard Choate" <rchoatecpa@NoSpam.com> wrote in message
> > news:e1RsSLMVDHA.3148@tk2msftngp13.phx.gbl...
> > > Just out of curiosity, is it really that important to keep those 2 values
>  in
> > > one cell? Your situation would be soooo much easier if you did that. As it
> > > is, you have to go through all of this trouble to separate them, and then,
> > > apparently, you want to perform some formatting.
> > > Richard Choate
> > >
> > > "Gav !!" <gavin.davidson@qr.com.au> wrote in message
> > > news:rI%Ua.137$U74.6997@news.optus.net.au...
> > > Folks
> > >
> > > 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 !!
> > >
> > >
> > >

Just select the cells that get the color adjustment--C1 in this case.

And in your original post, you had your data formatted like:  "1000 / 1400"

slashPos = InStr(1, myCell.Offset(0, -2).Value, " / ")

I think I'd go back and adjust my data to include a leading and trailing space
near the slashes.

If you decide to get rid of all the spaces (Edit|replace " " with nothing), then
try changing the line above to:

slashPos = InStr(1, myCell.Offset(0, -2).Value, "/")





I looked for space-slash-space in that cell.

"Gav !!" wrote:
> 
> Dave I have tried to run the code but can't seem to get it to work, if
> I use 3 columns say A,B & C and I put the following values in each
> 
> A1                B1              C1
> 1000/1400        1200            blank
> 
> then I highlight the 3 cells and run the macro - nothing happens
> 
> When I did a watch in the backend it says that
> 
> Set myRng = Selection
> 
>     For Each myCell In myRng.Cells
>         If myCell.Column < 3 Then
>             'do nothing
> 
> this part of the code only = 1 so it does nothing. Shouldn't it be
> picking up that I have selected 3 columns.
> 
> Thanks in advance Gav !!
> 
> Dave Peterson <ec35720@msn.com> wrote in message news:<3F25AF13.48D794FB@msn.com>...
> > And one more bad thing:  If your cell is going to contain a formula, then you
> > can't use that character by character color formatting.  It's whole cell at a
> > time.
> >
> > How about a macro that replaces the formulas.  This won't be dynamic which means
> > if you change the data, you'll have to rerun the macro.
> >
> > If yes, then I guessed that you have the 3 cells in a row.  The first two
> > columns are like E51 and F51.  I put the output in G51 (so that it always looks
> > at the preceding two cells.
> >
> > So select a bunch of cells and run this:
> >
> > Option Explicit
> > Sub fixemUp()
> >
> >     Dim tempVal0 As Double
> >     Dim tempVal1 As Double
> >     Dim tempVal2 As Double
> >     Dim LeftHandSide As String
> >     Dim RightHandSide As String
> >
> >     Dim slashPos As Long
> >     Dim myCell As Range
> >     Dim myRng As Range
> >
> >     Set myRng = Selection
> >
> >     For Each myCell In myRng.Cells
> >         If myCell.Column < 3 Then
> >             'do nothing
> >         Else
> >             If IsNumeric(myCell.Offset(0, -1).Value) Then
> >                 tempVal0 = myCell.Offset(0, -1).Value
> >                 slashPos = InStr(1, myCell.Offset(0, -2).Value, " / ")
> >                 If slashPos = 0 Then
> >                     'do nothing
> >                 Else
> >                     tempVal1 = CLng(Left(myCell.Offset(0, -2).Value, _
> >                                        slashPos - 1))
> >                     tempVal2 = CLng(Right(myCell.Offset(0, -2).Value, _
> >                                        slashPos - 1))
> >                     LeftHandSide = Format(tempVal1 / tempVal0 - 1, "00%")
> >                     RightHandSide = Format(tempVal2 / tempVal0 - 1, "00%")
> >                     myCell.Value = LeftHandSide & " / " & RightHandSide
> >                     myCell.Font.ColorIndex = xlAutomatic
> >
> >                     If Left(LeftHandSide, 1) = "-" Then
> >                         myCell.Characters(1, Len(LeftHandSide) - 1) _
> >                                 .Font.ColorIndex = 3
> >                     End If
> >                     If Left(RightHandSide, 1) = "-" Then
> >                         myCell.Characters(Len(myCell.Value) _
> >                               - Len(RightHandSide) + 1, _
> >                                       Len(RightHandSide) - 1) _
> >                                            .Font.ColorIndex = 3
> >                     End If
> >                 End If
> >             End If
> >         End If
> >     Next myCell
> >
> > End Sub
> >
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> >
> > "Gav !!" wrote:
> > >
> > > Managers make things difficult - Then I have to do difficult things to make
> > > their life easy
> > >
> > > Actually it can't be changed because the report has already been approved by
> > > senior executives and they like it, so now to stop incorrect figures being
> > > calculated this is what I have to work with.
> > >
> > > "Richard Choate" <rchoatecpa@NoSpam.com> wrote in message
> > > news:e1RsSLMVDHA.3148@tk2msftngp13.phx.gbl...
> > > > Just out of curiosity, is it really that important to keep those 2 values
> >  in
> > > > one cell? Your situation would be soooo much easier if you did that. As it
> > > > is, you have to go through all of this trouble to separate them, and then,
> > > > apparently, you want to perform some formatting.
> > > > Richard Choate
> > > >
> > > > "Gav !!" <gavin.davidson@qr.com.au> wrote in message
> > > > news:rI%Ua.137$U74.6997@news.optus.net.au...
> > > > Folks
> > > >
> > > > 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 !!
> > > >
> > > >
> > > >

-- 

Dave Peterson
ec35720@msn.com

Thanks Dave Works like a Dream - Your a Guru


Dave Peterson <ec35720@msn.com> wrote in message news:<3F2EE821.223AF5BA@msn.com>...
> Just select the cells that get the color adjustment--C1 in this case.
> 
> And in your original post, you had your data formatted like:  "1000 / 1400"
> 
> slashPos = InStr(1, myCell.Offset(0, -2).Value, " / ")
> 
> I think I'd go back and adjust my data to include a leading and trailing space
> near the slashes.
> 
> If you decide to get rid of all the spaces (Edit|replace " " with nothing), then
> try changing the line above to:
> 
> slashPos = InStr(1, myCell.Offset(0, -2).Value, "/")
> 
> 
> 
> 
> 
> I looked for space-slash-space in that cell.
> 
> "Gav !!" wrote:
> > 
> > Dave I have tried to run the code but can't seem to get it to work, if
> > I use 3 columns say A,B & C and I put the following values in each
> > 
> > A1                B1              C1
> > 1000/1400        1200            blank
> > 
> > then I highlight the 3 cells and run the macro - nothing happens
> > 
> > When I did a watch in the backend it says that
> > 
> > Set myRng = Selection
> > 
> >     For Each myCell In myRng.Cells
> >         If myCell.Column < 3 Then
> >             'do nothing
> > 
> > this part of the code only = 1 so it does nothing. Shouldn't it be
> > picking up that I have selected 3 columns.
> > 
> > Thanks in advance Gav !!
> > 
> > Dave Peterson <ec35720@msn.com> wrote in message news:<3F25AF13.48D794FB@msn.com>...
> > > And one more bad thing:  If your cell is going to contain a formula, then you
> > > can't use that character by character color formatting.  It's whole cell at a
> > > time.
> > >
> > > How about a macro that replaces the formulas.  This won't be dynamic which means
> > > if you change the data, you'll have to rerun the macro.
> > >
> > > If yes, then I guessed that you have the 3 cells in a row.  The first two
> > > columns are like E51 and F51.  I put the output in G51 (so that it always looks
> > > at the preceding two cells.
> > >
> > > So select a bunch of cells and run this:
> > >
> > > Option Explicit
> > > Sub fixemUp()
> > >
> > >     Dim tempVal0 As Double
> > >     Dim tempVal1 As Double
> > >     Dim tempVal2 As Double
> > >     Dim LeftHandSide As String
> > >     Dim RightHandSide As String
> > >
> > >     Dim slashPos As Long
> > >     Dim myCell As Range
> > >     Dim myRng As Range
> > >
> > >     Set myRng = Selection
> > >
> > >     For Each myCell In myRng.Cells
> > >         If myCell.Column < 3 Then
> > >             'do nothing
> > >         Else
> > >             If IsNumeric(myCell.Offset(0, -1).Value) Then
> > >                 tempVal0 = myCell.Offset(0, -1).Value
> > >                 slashPos = InStr(1, myCell.Offset(0, -2).Value, " / ")
> > >                 If slashPos = 0 Then
> > >                     'do nothing
> > >                 Else
> > >                     tempVal1 = CLng(Left(myCell.Offset(0, -2).Value, _
> > >                                        slashPos - 1))
> > >                     tempVal2 = CLng(Right(myCell.Offset(0, -2).Value, _
> > >                                        slashPos - 1))
> > >                     LeftHandSide = Format(tempVal1 / tempVal0 - 1, "00%")
> > >                     RightHandSide = Format(tempVal2 / tempVal0 - 1, "00%")
> > >                     myCell.Value = LeftHandSide & " / " & RightHandSide
> > >                     myCell.Font.ColorIndex = xlAutomatic
> > >
> > >                     If Left(LeftHandSide, 1) = "-" Then
> > >                         myCell.Characters(1, Len(LeftHandSide) - 1) _
> > >                                 .Font.ColorIndex = 3
> > >                     End If
> > >                     If Left(RightHandSide, 1) = "-" Then
> > >                         myCell.Characters(Len(myCell.Value) _
> > >                               - Len(RightHandSide) + 1, _
> > >                                       Len(RightHandSide) - 1) _
> > >                                            .Font.ColorIndex = 3
> > >                     End If
> > >                 End If
> > >             End If
> > >         End If
> > >     Next myCell
> > >
> > > End Sub
> > >
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > >
> > >
> > > "Gav !!" wrote:
> > > >
> > > > Managers make things difficult - Then I have to do difficult things to make
> > > > their life easy
> > > >
> > > > Actually it can't be changed because the report has already been approved by
> > > > senior executives and they like it, so now to stop incorrect figures being
> > > > calculated this is what I have to work with.
> > > >
> > > > "Richard Choate" <rchoatecpa@NoSpam.com> wrote in message
> > > > news:e1RsSLMVDHA.3148@tk2msftngp13.phx.gbl...
> > > > > Just out of curiosity, is it really that important to keep those 2 values
>  in
> > > > > one cell? Your situation would be soooo much easier if you did that. As it
> > > > > is, you have to go through all of this trouble to separate them, and then,
> > > > > apparently, you want to perform some formatting.
> > > > > Richard Choate
> > > > >
> > > > > "Gav !!" <gavin.davidson@qr.com.au> wrote in message
> > > > > news:rI%Ua.137$U74.6997@news.optus.net.au...
> > > > > Folks
> > > > >
> > > > > 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 !!
> > > > >
> > > > >
> > > > >