Prev: sum across worksheets conditionally
Next: How do I automatically hide columns?
From: Eric on 17 Mar 2010 22:53
Does anyone have any suggestions on how to change color within string?
For example, in cell B1, if(A1=1,"O-F-O-F","O-N-O-N"), I would like to the
last 3 chars into red color, such as O-F and O-N
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
From: Jacob Skaria on 18 Mar 2010 00:34
You cannot make a formula to change the formatting.

--
Jacob


"Eric" wrote:

> Does anyone have any suggestions on how to change color within string?
> For example, in cell B1, if(A1=1,"O-F-O-F","O-N-O-N"), I would like to the
> last 3 chars into red color, such as O-F and O-N
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
From: PBezucha on 18 Mar 2010 04:33
Definitely you cannot. But to be positive, there use to be sometimes some
roundabouts. In your simplest case you can make a fake function by means of a
worksheet events procedure in VBA. Try it on an example in a new workbook.
From a worksheet open the project (Alt-F11) and from the Projects window
select MicrosoftExcelObjects and your Worksheet name. By doubleclick open the
codemodule window and paste the following example. For Excel 7 the procedure
has been somewhat changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, S As String
Set A = Range("A:A")
If Not Intersect(Target, A) Is Nothing Then
If Target.Value > 0 Then S = "AAAAAA" Else S = "BBBBBB"
With Target.Offset(0, 1)
.Value = S
.Characters(Start:=4, Length:=3).Font.ColorIndex = 3
End With
End If
End Sub

Depending on the change in the first column you will get formatted contents
of the adjacent cells.
I hope you can tailor the example to your needs as soon as you will learn
more about events.

--
Petr Bezucha


"Eric" wrote:

> Does anyone have any suggestions on how to change color within string?
> For example, in cell B1, if(A1=1,"O-F-O-F","O-N-O-N"), I would like to the
> last 3 chars into red color, such as O-F and O-N
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
 | 
Pages: 1
Prev: sum across worksheets conditionally
Next: How do I automatically hide columns?