Code to bold names within a cell- With a twist

I have two problems, but first a little background:

I have a worksheet that lists names, followed by the names are about 5
columns, two of which are a "capabilities" and "results" ranking. Each of
these columns only has 5 possible answers that can be put in. On another page
I have a 5x5 grid with Capabilities Ranking on the Vertical Axis and Results
on the Horiz. My macro loops through the data sheet and the places each
person's name in the appropriate cell on the grid.

The code places the people one on top of another and re-sizes the cell so
that you can see everyone's name. SO, one cell in the grid may look like
this....

John
Sally
Sue
Bob

Here are the problems:

1. There is another column of data that I have added to my array that holds
the employee's "develop on date". If this date is less than a a year away, I
want their name to be bold on the grid. The obvious problem here is bolding
one person's name and not the entire cell.

2. If more than 30 people have the same ranking, the re-sizer will try to
re-size the row greater than 490.5 (not allowed by excel) and it will crash.
I think this is a tougher question than the first. How in the world can I
prevent this. Is there anyway to say if there are more than 29 people who fit
this description, double stack them, ie.
Bob Sally
John David
Stuart Bob

I really don't know, I have no clue as to how I am supposed to solve that
problem.


THANK YYOU SO MUCH IN ADVANCE!!!!!

Keep a record of who needs to be highlighted by name and location in the grid
(or run it as a separate program)

you can bold a part of a string with

Sub BoldThe()
s = "Running in the Woods is hard"
i = InStr(1, s, "the", vbTextCompare)
ActiveCell.Value = s
ActiveCell.Characters(i, 3).Font.Bold = True
ActiveCell.WrapText = True
ActiveCell.EntireRow.AutoFit
End Sub

as a demonstration. The above is probably applicable for your second
column. Pick a standard column width, just append your names with no
chr(10) and when done, autofit the rows. The names won't necessarily be
lined up in a columnar fashion. If you need columnar, then you two column
approach seems good. You might do better with a mono-spaced font like
courier new. Also using a smaller font size might help.

--
Regards,
Tom Ogilvy


"macro_guy34345" wrote:

> I have two problems, but first a little background:
>
> I have a worksheet that lists names, followed by the names are about 5
> columns, two of which are a "capabilities" and "results" ranking. Each of
> these columns only has 5 possible answers that can be put in. On another page
> I have a 5x5 grid with Capabilities Ranking on the Vertical Axis and Results
> on the Horiz. My macro loops through the data sheet and the places each
> person's name in the appropriate cell on the grid.
>
> The code places the people one on top of another and re-sizes the cell so
> that you can see everyone's name. SO, one cell in the grid may look like
> this....
>
> John
> Sally
> Sue
> Bob
>
> Here are the problems:
>
> 1. There is another column of data that I have added to my array that holds
> the employee's "develop on date". If this date is less than a a year away, I
> want their name to be bold on the grid. The obvious problem here is bolding
> one person's name and not the entire cell.
>
> 2. If more than 30 people have the same ranking, the re-sizer will try to
> re-size the row greater than 490.5 (not allowed by excel) and it will crash.
> I think this is a tougher question than the first. How in the world can I
> prevent this. Is there anyway to say if there are more than 29 people who fit
> this description, double stack them, ie.
> Bob Sally
> John David
> Stuart Bob
>
> I really don't know, I have no clue as to how I am supposed to solve that
> problem.
>
>
> THANK YYOU SO MUCH IN ADVANCE!!!!!
>

Well, that definitly helps, the problem is that this is going to be seen by a
lot of vice-prez's. So I need to formatting to look really good. Is there any
way to align the first name to the left of the cell and the second name to
the right of the cell?

Tom Jones Bob Denver
Dave Braine Meg Olvie

Something like that. I'll be honest, I am VERY new to vba. I am not really
sure what to do here. My boss definitly wants it to be in a columner format
and very professional looking.

Thanks again!
"Tom Ogilvy" wrote:

> Keep a record of who needs to be highlighted by name and location in the grid
> (or run it as a separate program)
>
> you can bold a part of a string with
>
> Sub BoldThe()
> s = "Running in the Woods is hard"
> i = InStr(1, s, "the", vbTextCompare)
> ActiveCell.Value = s
> ActiveCell.Characters(i, 3).Font.Bold = True
> ActiveCell.WrapText = True
> ActiveCell.EntireRow.AutoFit
> End Sub
>
> as a demonstration. The above is probably applicable for your second
> column. Pick a standard column width, just append your names with no
> chr(10) and when done, autofit the rows. The names won't necessarily be
> lined up in a columnar fashion. If you need columnar, then you two column
> approach seems good. You might do better with a mono-spaced font like
> courier new. Also using a smaller font size might help.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "macro_guy34345" wrote:
>
> > I have two problems, but first a little background:
> >
> > I have a worksheet that lists names, followed by the names are about 5
> > columns, two of which are a "capabilities" and "results" ranking. Each of
> > these columns only has 5 possible answers that can be put in. On another page
> > I have a 5x5 grid with Capabilities Ranking on the Vertical Axis and Results
> > on the Horiz. My macro loops through the data sheet and the places each
> > person's name in the appropriate cell on the grid.
> >
> > The code places the people one on top of another and re-sizes the cell so
> > that you can see everyone's name. SO, one cell in the grid may look like
> > this....
> >
> > John
> > Sally
> > Sue
> > Bob
> >
> > Here are the problems:
> >
> > 1. There is another column of data that I have added to my array that holds
> > the employee's "develop on date". If this date is less than a a year away, I
> > want their name to be bold on the grid. The obvious problem here is bolding
> > one person's name and not the entire cell.
> >
> > 2. If more than 30 people have the same ranking, the re-sizer will try to
> > re-size the row greater than 490.5 (not allowed by excel) and it will crash.
> > I think this is a tougher question than the first. How in the world can I
> > prevent this. Is there anyway to say if there are more than 29 people who fit
> > this description, double stack them, ie.
> > Bob Sally
> > John David
> > Stuart Bob
> >
> > I really don't know, I have no clue as to how I am supposed to solve that
> > problem.
> >
> >
> > THANK YYOU SO MUCH IN ADVANCE!!!!!
> >

There isn't a clean way to do what you want unless you want to pad the
characters with spaces and use a monospaced font.

The alternative might be to just write one name in a cell and space your
cells out so each current row will consist of multiple rows. then you hide
gridlines and apply boarderss to make these look like single cells. this
would allow you to stack as many names as you want in a virtual cell. You
could further expand it so that each current column would have 2 or more
columns and that would allow you to use your double (or more) columns of
names and they would be lined up. Again, visually, you use boarders to make
these look like individual large cells if that is the desire.

This would make bolding a name trivial as you would just bold that cell.
You could also make your virtual rows of uniform height and your vitual
columns of uniform width.

--
Regards,
Tom Ogilvy

"macro_guy34345" wrote:

> Well, that definitly helps, the problem is that this is going to be seen by a
> lot of vice-prez's. So I need to formatting to look really good. Is there any
> way to align the first name to the left of the cell and the second name to
> the right of the cell?
>
> Tom Jones Bob Denver
> Dave Braine Meg Olvie
>
> Something like that. I'll be honest, I am VERY new to vba. I am not really
> sure what to do here. My boss definitly wants it to be in a columner format
> and very professional looking.
>
> Thanks again!
> "Tom Ogilvy" wrote:
>
> > Keep a record of who needs to be highlighted by name and location in the grid
> > (or run it as a separate program)
> >
> > you can bold a part of a string with
> >
> > Sub BoldThe()
> > s = "Running in the Woods is hard"
> > i = InStr(1, s, "the", vbTextCompare)
> > ActiveCell.Value = s
> > ActiveCell.Characters(i, 3).Font.Bold = True
> > ActiveCell.WrapText = True
> > ActiveCell.EntireRow.AutoFit
> > End Sub
> >
> > as a demonstration. The above is probably applicable for your second
> > column. Pick a standard column width, just append your names with no
> > chr(10) and when done, autofit the rows. The names won't necessarily be
> > lined up in a columnar fashion. If you need columnar, then you two column
> > approach seems good. You might do better with a mono-spaced font like
> > courier new. Also using a smaller font size might help.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "macro_guy34345" wrote:
> >
> > > I have two problems, but first a little background:
> > >
> > > I have a worksheet that lists names, followed by the names are about 5
> > > columns, two of which are a "capabilities" and "results" ranking. Each of
> > > these columns only has 5 possible answers that can be put in. On another page
> > > I have a 5x5 grid with Capabilities Ranking on the Vertical Axis and Results
> > > on the Horiz. My macro loops through the data sheet and the places each
> > > person's name in the appropriate cell on the grid.
> > >
> > > The code places the people one on top of another and re-sizes the cell so
> > > that you can see everyone's name. SO, one cell in the grid may look like
> > > this....
> > >
> > > John
> > > Sally
> > > Sue
> > > Bob
> > >
> > > Here are the problems:
> > >
> > > 1. There is another column of data that I have added to my array that holds
> > > the employee's "develop on date". If this date is less than a a year away, I
> > > want their name to be bold on the grid. The obvious problem here is bolding
> > > one person's name and not the entire cell.
> > >
> > > 2. If more than 30 people have the same ranking, the re-sizer will try to
> > > re-size the row greater than 490.5 (not allowed by excel) and it will crash.
> > > I think this is a tougher question than the first. How in the world can I
> > > prevent this. Is there anyway to say if there are more than 29 people who fit
> > > this description, double stack them, ie.
> > > Bob Sally
> > > John David
> > > Stuart Bob
> > >
> > > I really don't know, I have no clue as to how I am supposed to solve that
> > > problem.
> > >
> > >
> > > THANK YYOU SO MUCH IN ADVANCE!!!!!
> > >

How do I pad them with spaces and do the monospace font? I have no idea what
that means, but if that would make this possible, then I am game.

Thanks!

"Tom Ogilvy" wrote:

> There isn't a clean way to do what you want unless you want to pad the
> characters with spaces and use a monospaced font.
>
> The alternative might be to just write one name in a cell and space your
> cells out so each current row will consist of multiple rows. then you hide
> gridlines and apply boarderss to make these look like single cells. this
> would allow you to stack as many names as you want in a virtual cell. You
> could further expand it so that each current column would have 2 or more
> columns and that would allow you to use your double (or more) columns of
> names and they would be lined up. Again, visually, you use boarders to make
> these look like individual large cells if that is the desire.
>
> This would make bolding a name trivial as you would just bold that cell.
> You could also make your virtual rows of uniform height and your vitual
> columns of uniform width.
>
> --
> Regards,
> Tom Ogilvy
>
> "macro_guy34345" wrote:
>
> > Well, that definitly helps, the problem is that this is going to be seen by a
> > lot of vice-prez's. So I need to formatting to look really good. Is there any
> > way to align the first name to the left of the cell and the second name to
> > the right of the cell?
> >
> > Tom Jones Bob Denver
> > Dave Braine Meg Olvie
> >
> > Something like that. I'll be honest, I am VERY new to vba. I am not really
> > sure what to do here. My boss definitly wants it to be in a columner format
> > and very professional looking.
> >
> > Thanks again!
> > "Tom Ogilvy" wrote:
> >
> > > Keep a record of who needs to be highlighted by name and location in the grid
> > > (or run it as a separate program)
> > >
> > > you can bold a part of a string with
> > >
> > > Sub BoldThe()
> > > s = "Running in the Woods is hard"
> > > i = InStr(1, s, "the", vbTextCompare)
> > > ActiveCell.Value = s
> > > ActiveCell.Characters(i, 3).Font.Bold = True
> > > ActiveCell.WrapText = True
> > > ActiveCell.EntireRow.AutoFit
> > > End Sub
> > >
> > > as a demonstration. The above is probably applicable for your second
> > > column. Pick a standard column width, just append your names with no
> > > chr(10) and when done, autofit the rows. The names won't necessarily be
> > > lined up in a columnar fashion. If you need columnar, then you two column
> > > approach seems good. You might do better with a mono-spaced font like
> > > courier new. Also using a smaller font size might help.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "macro_guy34345" wrote:
> > >
> > > > I have two problems, but first a little background:
> > > >
> > > > I have a worksheet that lists names, followed by the names are about 5
> > > > columns, two of which are a "capabilities" and "results" ranking. Each of
> > > > these columns only has 5 possible answers that can be put in. On another page
> > > > I have a 5x5 grid with Capabilities Ranking on the Vertical Axis and Results
> > > > on the Horiz. My macro loops through the data sheet and the places each
> > > > person's name in the appropriate cell on the grid.
> > > >
> > > > The code places the people one on top of another and re-sizes the cell so
> > > > that you can see everyone's name. SO, one cell in the grid may look like
> > > > this....
> > > >
> > > > John
> > > > Sally
> > > > Sue
> > > > Bob
> > > >
> > > > Here are the problems:
> > > >
> > > > 1. There is another column of data that I have added to my array that holds
> > > > the employee's "develop on date". If this date is less than a a year away, I
> > > > want their name to be bold on the grid. The obvious problem here is bolding
> > > > one person's name and not the entire cell.
> > > >
> > > > 2. If more than 30 people have the same ranking, the re-sizer will try to
> > > > re-size the row greater than 490.5 (not allowed by excel) and it will crash.
> > > > I think this is a tougher question than the first. How in the world can I
> > > > prevent this. Is there anyway to say if there are more than 29 people who fit
> > > > this description, double stack them, ie.
> > > > Bob Sally
> > > > John David
> > > > Stuart Bob
> > > >
> > > > I really don't know, I have no clue as to how I am supposed to solve that
> > > > problem.
> > > >
> > > >
> > > > THANK YYOU SO MUCH IN ADVANCE!!!!!
> > > >

Courier new is a monospaced font. That means that an "i" takes up the same
amount of space/width as "W". So you would have to determine the most
characters you will need going across two columns of names (including some
spaces to separate them, then adjust every line so it is as wide as is
needed.

mmm mmmmm mmmmm mmmmmm
mm mmm mmm mmmmm
mmm mmmm mmmm mmm
mm mmmmm mm mmm
mmmmm mmm mm mm

on my screen these are pretty well lined up. The spaces added are the
padding.

--
Regards,
Tom Ogilvy


"macro_guy34345" <[email protected]> wrote in message
news:[email protected]...
> How do I pad them with spaces and do the monospace font? I have no idea
what
> that means, but if that would make this possible, then I am game.
>
> Thanks!
>
> "Tom Ogilvy" wrote:
>
> > There isn't a clean way to do what you want unless you want to pad the
> > characters with spaces and use a monospaced font.
> >
> > The alternative might be to just write one name in a cell and space your
> > cells out so each current row will consist of multiple rows. then you
hide
> > gridlines and apply boarderss to make these look like single cells.
this
> > would allow you to stack as many names as you want in a virtual cell.
You
> > could further expand it so that each current column would have 2 or more
> > columns and that would allow you to use your double (or more) columns of
> > names and they would be lined up. Again, visually, you use boarders to
make
> > these look like individual large cells if that is the desire.
> >
> > This would make bolding a name trivial as you would just bold that cell.
> > You could also make your virtual rows of uniform height and your vitual
> > columns of uniform width.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "macro_guy34345" wrote:
> >
> > > Well, that definitly helps, the problem is that this is going to be
seen by a
> > > lot of vice-prez's. So I need to formatting to look really good. Is
there any
> > > way to align the first name to the left of the cell and the second
name to
> > > the right of the cell?
> > >
> > > Tom Jones Bob Denver
> > > Dave Braine Meg Olvie
> > >
> > > Something like that. I'll be honest, I am VERY new to vba. I am not
really
> > > sure what to do here. My boss definitly wants it to be in a columner
format
> > > and very professional looking.
> > >
> > > Thanks again!
> > > "Tom Ogilvy" wrote:
> > >
> > > > Keep a record of who needs to be highlighted by name and location in
the grid
> > > > (or run it as a separate program)
> > > >
> > > > you can bold a part of a string with
> > > >
> > > > Sub BoldThe()
> > > > s = "Running in the Woods is hard"
> > > > i = InStr(1, s, "the", vbTextCompare)
> > > > ActiveCell.Value = s
> > > > ActiveCell.Characters(i, 3).Font.Bold = True
> > > > ActiveCell.WrapText = True
> > > > ActiveCell.EntireRow.AutoFit
> > > > End Sub
> > > >
> > > > as a demonstration. The above is probably applicable for your
second
> > > > column. Pick a standard column width, just append your names with
no
> > > > chr(10) and when done, autofit the rows. The names won't
necessarily be
> > > > lined up in a columnar fashion. If you need columnar, then you two
column
> > > > approach seems good. You might do better with a mono-spaced font
like
> > > > courier new. Also using a smaller font size might help.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "macro_guy34345" wrote:
> > > >
> > > > > I have two problems, but first a little background:
> > > > >
> > > > > I have a worksheet that lists names, followed by the names are
about 5
> > > > > columns, two of which are a "capabilities" and "results" ranking.
Each of
> > > > > these columns only has 5 possible answers that can be put in. On
another page
> > > > > I have a 5x5 grid with Capabilities Ranking on the Vertical Axis
and Results
> > > > > on the Horiz. My macro loops through the data sheet and the places
each
> > > > > person's name in the appropriate cell on the grid.
> > > > >
> > > > > The code places the people one on top of another and re-sizes the
cell so
> > > > > that you can see everyone's name. SO, one cell in the grid may
look like
> > > > > this....
> > > > >
> > > > > John
> > > > > Sally
> > > > > Sue
> > > > > Bob
> > > > >
> > > > > Here are the problems:
> > > > >
> > > > > 1. There is another column of data that I have added to my array
that holds
> > > > > the employee's "develop on date". If this date is less than a a
year away, I
> > > > > want their name to be bold on the grid. The obvious problem here
is bolding
> > > > > one person's name and not the entire cell.
> > > > >
> > > > > 2. If more than 30 people have the same ranking, the re-sizer will
try to
> > > > > re-size the row greater than 490.5 (not allowed by excel) and it
will crash.
> > > > > I think this is a tougher question than the first. How in the
world can I
> > > > > prevent this. Is there anyway to say if there are more than 29
people who fit
> > > > > this description, double stack them, ie.
> > > > > Bob Sally
> > > > > John David
> > > > > Stuart Bob
> > > > >
> > > > > I really don't know, I have no clue as to how I am supposed to
solve that
> > > > > problem.
> > > > >
> > > > >
> > > > > THANK YYOU SO MUCH IN ADVANCE!!!!!
> > > > >

Tom's suggestion of using a mono space font would be easiest. But if you
know you will need two columns why not use two real columns.

Following puts all cells in A1:A10 in two cells in a row. I assume you have
some method of getting the actual cell values but just for ideas -

Option Explicit
Sub testSetup()
Dim i As Long, j As Long
Dim s As String
For i = 1 To 10
s = ""
For j = 1 To i
s = s & Chr(i + 64)
Next
Cells(i, 1) = s
Next

End Sub

Sub test()
Dim i As Long
Dim w As Double
Dim s1 As String, s2 As String

For i = 1 To 10
If i Mod 2 Then
If Len(s1) Then s1 = s1 & vbLf
s1 = s1 & Cells(i, 1)
Else
If Len(s2) Then s2 = s2 & vbLf
s2 = s2 & Cells(i, 1)
End If
Next

With Columns(1)
.AutoFit
w = .ColumnWidth
End With

Range("c11").Value = s1
Range("d11").Value = s2
Range("C:D").ColumnWidth = w

End Sub

Regards,
Peter T

"macro_guy34345" <[email protected]> wrote in message
news:[email protected]...
> Well, that definitly helps, the problem is that this is going to be seen
by a
> lot of vice-prez's. So I need to formatting to look really good. Is there
any
> way to align the first name to the left of the cell and the second name to
> the right of the cell?
>
> Tom Jones Bob Denver
> Dave Braine Meg Olvie
>
> Something like that. I'll be honest, I am VERY new to vba. I am not really
> sure what to do here. My boss definitly wants it to be in a columner
format
> and very professional looking.
>
> Thanks again!
> "Tom Ogilvy" wrote:
>
> > Keep a record of who needs to be highlighted by name and location in the
grid
> > (or run it as a separate program)
> >
> > you can bold a part of a string with
> >
> > Sub BoldThe()
> > s = "Running in the Woods is hard"
> > i = InStr(1, s, "the", vbTextCompare)
> > ActiveCell.Value = s
> > ActiveCell.Characters(i, 3).Font.Bold = True
> > ActiveCell.WrapText = True
> > ActiveCell.EntireRow.AutoFit
> > End Sub
> >
> > as a demonstration. The above is probably applicable for your second
> > column. Pick a standard column width, just append your names with no
> > chr(10) and when done, autofit the rows. The names won't necessarily be
> > lined up in a columnar fashion. If you need columnar, then you two
column
> > approach seems good. You might do better with a mono-spaced font like
> > courier new. Also using a smaller font size might help.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "macro_guy34345" wrote:
> >
> > > I have two problems, but first a little background:
> > >
> > > I have a worksheet that lists names, followed by the names are about 5
> > > columns, two of which are a "capabilities" and "results" ranking. Each
of
> > > these columns only has 5 possible answers that can be put in. On
another page
> > > I have a 5x5 grid with Capabilities Ranking on the Vertical Axis and
Results
> > > on the Horiz. My macro loops through the data sheet and the places
each
> > > person's name in the appropriate cell on the grid.
> > >
> > > The code places the people one on top of another and re-sizes the cell
so
> > > that you can see everyone's name. SO, one cell in the grid may look
like
> > > this....
> > >
> > > John
> > > Sally
> > > Sue
> > > Bob
> > >
> > > Here are the problems:
> > >
> > > 1. There is another column of data that I have added to my array that
holds
> > > the employee's "develop on date". If this date is less than a a year
away, I
> > > want their name to be bold on the grid. The obvious problem here is
bolding
> > > one person's name and not the entire cell.
> > >
> > > 2. If more than 30 people have the same ranking, the re-sizer will try
to
> > > re-size the row greater than 490.5 (not allowed by excel) and it will
crash.
> > > I think this is a tougher question than the first. How in the world
can I
> > > prevent this. Is there anyway to say if there are more than 29 people
who fit
> > > this description, double stack them, ie.
> > > Bob Sally
> > > John David
> > > Stuart Bob
> > >
> > > I really don't know, I have no clue as to how I am supposed to solve
that
> > > problem.
> > >
> > >
> > > THANK YYOU SO MUCH IN ADVANCE!!!!!
> > >