Drawing Lines w/ Cell Values

[Gerard]

Hello - Does anyone know how to input value into a cell say "A1 =2" and have that cell draw a line two blocks wide in cells B1 and C1.

 

[Peter_SSs]

Hello - Does anyone know how to input value into a cell say "A1 =2" and have that cell draw a line two blocks wide in cells B1 and C1.

Gerard

Welcome to the MrExcel board!

This uses Conditional Formatting. Is that what you want?

Post back if you need help with how to implement this.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	2
2	4
3	1
4	0
5	6
6
Draw Blocks

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =COLUMNS($B1:B1)<=$A1	Abc

 

[Gerard]

Peter,

Thanks for the speedy reply, what I'm looking for is the physical lines next to "autoshape" button in the bottom of the screen. I want to have a value in A1 = 1 and see a solid line through B1 using the drawn line.

 

[Peter_SSs]

Peter,

Thanks for the speedy reply, what I'm looking for is the physical lines next to "autoshape" button in the bottom of the screen. I want to have a value in A1 = 1 and see a solid line through B1 using the drawn line.

Hmm, see if this does what you want then?

If you need help with how to implement this, post back and include details of whether you want this to happen as a "one-off" or each time a cell in column A is changed etc.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DrawLine()<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> Shape<br>    <SPAN style="color:#00007F">Dim</SPAN> ColW() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, RowH() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lastr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    lastr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("A1:A" & lastr)<br>    lastc = 1 + WorksheetFunction.Max(myRange)<br>    <SPAN style="color:#00007F">ReDim</SPAN> RowH(lastr)<br>    <SPAN style="color:#00007F">ReDim</SPAN> ColW(lastc)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lastr<br>        RowH(i) = RowH(i - 1) + Rows(i).Height<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lastc<br>        ColW(i) = ColW(i - 1) + Columns(i).Width<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(c) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Sh <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Shapes<br>                <SPAN style="color:#00007F">If</SPAN> Sh.Top > RowH(c.Row - 1) And Sh.Top < RowH(c.Row) _<br>                        <SPAN style="color:#00007F">Or</SPAN> Sh.Top > RowH(lastr) <SPAN style="color:#00007F">Then</SPAN><br>                    Sh.Delete<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> Sh<br>            ActiveSheet.Shapes.AddLine(ColW(1), (RowH(c.Row - 1) + RowH(c.Row)) / 2, _<br>                ColW(c.Value + 1), (RowH(c.Row - 1) + RowH(c.Row)) / 2).Select<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    ActiveCell.Activate<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> DrawLineold()</FONT>

 

[Gerard]

In the event that I wanted to format the lines weight or change the line to say squiggly I am assuming I would do it in this set of code:


For Each c In myRange
If IsNumeric(c) Then
For Each Sh In ActiveSheet.Shapes
If Sh.Top > RowH(c.Row - 1) And Sh.Top < RowH(c.Row) _
Or Sh.Top > RowH(lastr) Then
Sh.Delete
End If
Next Sh
ActiveSheet.Shapes.AddLine(ColW(1), (RowH(c.Row - 1) + RowH(c.Row)) / 2, _
ColW(c.Value + 1), (RowH(c.Row - 1) + RowH(c.Row)) / 2).Select

But were specifically - as you can tell I'm a VB amateur

 

[Peter_SSs]

Squiggly? I'm not sure about that, but you can alter the characteristics of the line by replacing this line:

ActiveSheet.Shapes.AddLine(ColW(2, 1), (RowH(2, c.Row - 1) + RowH(2, c.Row)) / 2, _
                ColW(2, c.Value + 1), (RowH(2, c.Row - 1) + RowH(2, c.Row)) / 2).Select

With this section:

With ActiveSheet.Shapes.AddLine(ColW(1), (RowH(c.Row - 1) + RowH(c.Row)) / 2, _
        ColW(c.Value + 1), (RowH(c.Row - 1) + RowH(c.Row)) / 2).Line
    .Weight = 10
    .Pattern = msoPatternZigZag
End With

Where I have .Weight and .Pattern you can change values and/or add/delete properties in there. You would need to do a bit of searching around in the vba Help to look for the different options. Another way is to record a short macro of things you are interested in and look at the code to extract bits/ideas for your main code.

Also, when posting code, it makes it easier for others to read if you use code tags. That is, put [ code ] before and [ / code ] after your code (without the spaces). That way the code retains its indentation format as above. An alternative is to use the VBHTML Maker which produces code like in my earlier post.

 

[Gerard]

Thank You