Text function - Custom format

  • Thread starter Thread starter Gav !!
  • Start date Start date
G

Gav !!

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

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.
 
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
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.
 
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 !! said:
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 said:
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

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 !!
Click to expand...
Click to expand...
 
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 said:
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 !! said:
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 said:
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

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 !!
Click to expand...
Click to expand...
Click to expand...
 
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 !! said:
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 said:
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 !! said:
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.

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

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 !!
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave Works like a Dream - Your a Guru


Dave Peterson said:
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 !! said:
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 said:
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



:

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.

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

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 !!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top