Format for underlining a cell

[willjohnson33]

Ok, I have a problem that I am trying to solve. Range("D3") has the
number 2004. The width of column D is 30. I need to keep the number
2004 as a number not as a text value. If I keep it as a number then
underline the cell using the single accounting underline it only
underlines the number. I want the number accross the majority of the
cell. Similar to using a word table and using the single line
underline in "paragraph" not cell. To show what I am looking for you
can just change the 2004 to be text instead of a number and the line
will be exactly what I am looking for. Is there any way to use VBA to
create an underline that goes across almost all of the cell. I want to
have a little space so when I use the underline across multiple columns
it is not a connected line.

Thanks in advance.
Will

 

[Guest]

I don't think there is such a capability.

I would use the bottom border on that cell, then insert narrow blank columns
on each side of this column ti achive the gap (assumes you have turned off
gridlines).

 

[Guest]

This is an excellent question.

Enter 2004 in a cell and center it so empty space is on either side.

Then pull-down Format > Cell > Number > Custom

and in place of General enter " "General" "

Then format as underlined. You will notice that the underlining includes
the spaces to the right and left of the number.

Adjust the number of spaces between the double quotes to get the desired
appearance for your font and column width

 

[willjohnson33]

Thanks Gary's Student. I see what you are doing, but now my question
is: Is there a way to code it in VBA so that it automatically adjusts
the spaces to the width of the column? I'm assuming that there is some
ratio of characters per column width but I am just curious how to do it
in VBA. I basically am just creating an underline toggle that scrolls
through a few different options as far as color/thikness etc...


Thanks

 

[Guest]

The technique is easy. In any instance we know the LEN() of the number in
the cell. We we can make a string array:

" ", " ", " "
etc.

Based upon LEN, we choose either more or less blanks.

The trick is that the number of blanks we need will vary by font. A litttle
experimentation will show you that numbers in Verdana will need less blanks
than numbers in Arial Narrow.

A worse problem applies to words (as opposed to numbers) if proportional
fonts are used.

 

[willjohnson33]

I don't think it is as easy as you do. I understand how to get the
length of the number that is in the cell, but what is more important to
me is that when I toggle the underline it uses enough spaces to take up
95% of the cell. It needs to be consistent so that I can use it just
as easily between cells that are 10 wide or 30 wide. I hope I am
explaining myself correctly.

Thanks