Roland Garton
asked on
Make text in cell touch borders - remove margins
I often use Excel for charts, including project management charts. Ideally, I would like two or more adjacent cells in the same row to show an unbroken bar, slightly shorter than the height of the cells. I would like this bar to be shown conditionally, based on information elsewhere in the spreadsheet.
For example, if I put a "3" in a column indicating a 3-month task, I would like the next three cells to the right to contain this continuous bar. If I change the number to "4," I would like the bar to go across 4 cells. If this isn't clear, I can work up a quick example. I know how to do the conditional formulas and conditional formatting; it's the default formatting in Excel that I can't seem to override to make a bar continuous.
Apparently I cannot insert graphics into a cell. Also I cannot find a font with a solid bar as a substitute. But even if I could, the solid bar would not be connected between cells. But if I could remove the default margins so that text in a cell would touch the cell borders, then I might find a close-enough font, or even use a bunch of equal signs, to fake a continuous bar.
I'm open to suggestions how to accomplish this effect. I've considered VBA code that manipulates the length of rectangle shapes, but I'd rather keep it simple and use fonts and formats and formulas if possible.
For example, if I put a "3" in a column indicating a 3-month task, I would like the next three cells to the right to contain this continuous bar. If I change the number to "4," I would like the bar to go across 4 cells. If this isn't clear, I can work up a quick example. I know how to do the conditional formulas and conditional formatting; it's the default formatting in Excel that I can't seem to override to make a bar continuous.
Apparently I cannot insert graphics into a cell. Also I cannot find a font with a solid bar as a substitute. But even if I could, the solid bar would not be connected between cells. But if I could remove the default margins so that text in a cell would touch the cell borders, then I might find a close-enough font, or even use a bunch of equal signs, to fake a continuous bar.
I'm open to suggestions how to accomplish this effect. I've considered VBA code that manipulates the length of rectangle shapes, but I'd rather keep it simple and use fonts and formats and formulas if possible.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
See if this is close to your situation
https://www.experts-exchange.com/questions/26913281/Macro-to-automatically-insert-a-bar-in-each-individual-cell-or-collectively.html
Saqib
https://www.experts-exchange.com/questions/26913281/Macro-to-automatically-insert-a-bar-in-each-individual-cell-or-collectively.html
Saqib
ASKER
Workable - will use.
ASKER
[Wish I could edit previous comment - it was incomplete.]
Thanks, all.
Curt's solution is almost exactly what I want, except that it renders bars that fade from top to bottom instead of crisp, clear box borders. I'll use this one.
Rob's solution still leaves the gap, as he pointed out. Nice to know about that font, though. It's not on my system. By the way, easier way to put repeating characters in a cell is to apply a custom format. I can do repeating plus signs with the custom format @*+.
Saqib's reference would probably work, but it would involve a lot of VBA code that I was hoping to avoid.
Apparently, it's impossible to make the text touch the left or right cell border.
Thanks, all.
Curt's solution is almost exactly what I want, except that it renders bars that fade from top to bottom instead of crisp, clear box borders. I'll use this one.
Rob's solution still leaves the gap, as he pointed out. Nice to know about that font, though. It's not on my system. By the way, easier way to put repeating characters in a cell is to apply a custom format. I can do repeating plus signs with the custom format @*+.
Saqib's reference would probably work, but it would involve a lot of VBA code that I was hoping to avoid.
Apparently, it's impossible to make the text touch the left or right cell border.
To fill cell use formula:
=REPT(4,cell("width"))
Still leaves a small gap!!
Cheers
Rob H
Rob H