Change color of characters in a cell based of a cell reference

[kimbobo]

Hi All,

Does anyone know how to change the color of characters in a cell based
of a cell reference.

For example, cell A1 = "||||||||||" (contains 10 "|") I want the first
4 "|" to be Red and the second 6 to Blue. Based of cells A2 = 4 and A3
= 6.

Any ideas?

Thanks!!

-Kim

 

[ND Pard]

The following subprocedure (macro) will color the charactacters in column A,
beginning with cell A1 and going down until the value in column A is less
than " ", to the desired colors Red and Blue based on the column A's
respective values in columns B and C:

Sub Chg_Colors()
Range("A1").Select

Do While ActiveCell.Value > " "
ActiveCell.Characters(Start:=1, _
Length:=ActiveCell.Offset(0, 1).Value).Font.Color = -16777024
ActiveCell.Characters(Start:=ActiveCell.Offset(0, 1).Value + 1, _
Length:=ActiveCell.Offset(0, 2).Value).Font.Color = -10477568
ActiveCell.Offset(1).Select
Loop
End Sub

Good Luck.

 

[OssieMac]

Not sure if you wanted worksheet functions or VBA to do this and if you want
VBA, if you need further instructions and also how you want to initiate the
running of the macros to change the fonts. The following two activate on
changes to cells. (You can only use one of them. See the comments following
the sub name for what activates them).

Right click on the tab sheet name and select View Code and copy one of the
macros into the VAB editor. To close the VBA editor, click on the X with the
red background top right of VBA editor window.

First Option:-
Private Sub Worksheet_Change(ByVal Target As Range)
'Changes the colors of Cell A1 based on a change to the cell A1
Select Case Target
Case Range("A1")
With Target
.Characters(1, Cells(2, "A")).Font.Color = vbRed
.Characters(5, Cells(3, "A")).Font.Color = vbBlue
End With
Case Range("B1")
'Another option here
Case Range("C1")
'Another option here
End Select
End Sub


Second option:-
Private Sub Worksheet_Change(ByVal Target As Range)
'Changes the colors based on a change to either A2 or A3
Select Case Target
Case Range("A2"), Range("A3")
With Range("A1")
.Characters(1, Cells(2, "A")).Font.Color = vbRed
.Characters(5, Cells(3, "A")).Font.Color = vbBlue
End With

Case Range("A4"), Range("A5")
'Further options here if required

End Select

End Sub