Good morning all,

I have a program that I'm working on and it was going well till I hit the wall. I need to paste cell content as comments in about 1000 cells (all in the same column). I've figured out how to paste the cell content into the comment boxes. The problem is I need all the comment boxes to be formatted in a particular way. I need to have the comments formatted into two columns, with 8 spaces after the colon (if there was a way to left justify both columns so i wouldn't need to put a specific number of spaces in between them that would be a huge bonus), and the words in the second column bolded. Here's a sample of what the info going into the comment boxes will look like.

Aircraft: Boeing
Model: 777-200
Engine: GE-90B
Maint Cycle: 4X1BB
Base: KDEN

The challenge is that I need spaces between the label (Aircraft) and the variant (Boeing) and so on down the list, I also need all the variants (everything after the colon to be bolded).

I found the code below with google and it comes close to doing what I need, with a few differences, the code below formats the information in the cell itself, not the info in the comment box. The other problem is that this code formats the letters before the colon and not after the colon like I need. I would really appreciate it if someone could modify this code for me, it would be a HUGE help!!!!!

Thanks for any help;






Sub FormatActiveCells()
Dim c As Range
For Each c In Selection.Cells
FormatCell c
Next
End Sub

' This subroutine does the work
Sub FormatCell(c As Range)
Dim pos1 As Integer, pos2 As Integer
' Determine if line 1 is Symbol
pos1 = InStr(1, c.Text, "Symbol:")
If pos1 > 0 Then
' Make "Symbol:" bold
c.Characters(pos1, Len("Symbol:")).Font.FontStyle = "Bold"
' Advance past colon character
pos1 = pos1 + Len("Symbol:")
' Find end-of-line character
pos2 = InStr(pos1, c.Text, Chr(10))
' Make text between "Symbol:" and end-line italicized and colored
c.Characters(pos1, pos2 - pos1).Font.FontStyle = "Bold Italic"
c.Characters(pos1, pos2 - pos1).Font.Color = RGB(0, 0, 255)
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
Else
' Point both positions to first character
pos1 = 1
pos2 = 1
End If

' Format additional lines
Do
' Find colon character
pos2 = InStr(pos1, c.Text, ":")
' If not found, we're done
If pos2 = 0 Then Exit Do
' Make text from start of line to colon bold
c.Characters(Start:=pos1, Length:=pos2 - pos1).Font.FontStyle = "Bold"
' Find end-of-line
pos2 = InStr(pos2 + 1, c.Text, Chr(10))
' If not found, we're done
If pos2 = 0 Then Exit Do
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
DoEvents
Loop
End Sub

Sub FormatActiveCells_1()
Dim cmt As Comment
For Each Comment In Selection.Cell
FormatCell cmt
Next
End Sub

' This subroutine does the work
Sub FormatComment(c As Range)
Dim pos1 As Integer, pos2 As Integer
' Determine if line 1 is Symbol
pos1 = InStr(1, c.Text, "Symbol:")
If pos1 > 0 Then
' Make "Symbol:" bold
c.Characters(pos1, Len("Symbol:")).Font.FontStyle = "Bold"
' Advance past colon character
pos1 = pos1 + Len("Symbol:")
' Find end-of-line character
pos2 = InStr(pos1, c.Text, Chr(10))
' Make text between "Symbol:" and end-line italicized and colored
c.Characters(pos1, pos2 - pos1).Font.FontStyle = "Bold Italic"
c.Characters(pos1, pos2 - pos1).Font.Color = RGB(0, 0, 255)
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
Else
' Point both positions to first character
pos1 = 1
pos2 = 1
End If

' Format additional lines
Do
' Find colon character
pos2 = InStr(pos1, c.Text, ":")
' If not found, we're done
If pos2 = 0 Then Exit Do
' Make text from start of line to colon bold
c.Characters(Start:=pos1, Length:=pos2 - pos1).Font.FontStyle = "Bold"
' Find end-of-line
pos2 = InStr(pos2 + 1, c.Text, Chr(10))
' If not found, we're done
If pos2 = 0 Then Exit Do
' Point both positions to one character past end-of-line
pos2 = pos2 + 1
pos1 = pos2
DoEvents
Loop
End Sub