Excel: Change color of PART of a cell 3

[PaulBeckett]

I want to use VB to change the color of a portion of the text in a cell. I have only been able to find methods that allow me to change the color of all the text in a cell.

Example:
Where a cell contains:
ACATG[C]TCAGATAAAAAGCTGCAAGG[A]AAATAAAAAAGGAGTG[A]G[-]AGTTCA[G]TTTTC[C]TCA

I want to be able to change the color and boldness of the text that is between the square braces.

Thanks in Advance,

Paul

 

[joxo1]

When you say Cell, do you mean in excel?

 

[PaulBeckett]

Yes. I want to change the text colour of just part of a cell in an excel spreadsheet.

 

[SkipVought]

Hi,

The Macro Recorde is a powerful tool for doing stuff NAD learning.

Turn on the Macro Recorder, and then edit your cell, selecting the text of interest and formatting as desired.

Turn off the macro recorder and observe your code. It will need to be modified to work. Post bakc with these results and we'll go from there.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[Menthos]

Just recorded a very quick test macro and this is what it came back with (I've deleted the non-important properties and added a comment for the colour).
Shouldn't be too tricky at all to use this method to colour the relevant bits of your cell text.


With Worksheets(1).cells(1,1).Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 3 ' Red
End With

Hope that helps.

Menthos

 

[strongm]

Check out the Range object's Characters property (where the range you are interested in is a single cell)

 

[SkipVought]

So you need to find the POSITION of [ and ] using instr

s1 = 1
YourValue = Worksheets(1).cells(1,1).Value
do
  p1 = instr(s1, YourValue, "[")
  if p1 = 0 then exit do
'i assume that there will ALWAYS be a pair of bracksts
  p2 = instr(p1, YourValue, "]")
  With Worksheets(1).cells(1,1).Characters(Start:=p1+1, Length:=p2-p1).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .ColorIndex = 3          ' Red
   End With
  s1 = p2+1
loop

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[PaulBeckett]

Thanks everyone. Especial thanks to Menthos and SkipVought. That code is fantastic, it does exactly what I want! It's really going to help me immensely.

Cheers,

Paul

 

[strongm]

Here's an alternative:

Sub BoldRedActiveCell()
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "(\[.*?\])"
        For Each myMatch In .Execute(ActiveCell.Value)
            With ActiveCell.Characters(Start:=myMatch.FirstIndex + 2, Length:=myMatch.Length - 2).Font
                .Color = vbRed
                .Bold = True
            End With
        Next
    End With
End Sub