Conditional formula to draw lines at 45, 90 and 180 degree

[ta7009]

I have an excel file containing values from the Square of Nine and would like to add conditional formatting to include:
1. horizontal line at 180 degree angle
2. Vertical line at 90 degree angle
3. diagonal line at 45 degree angle

Can you help formulate a formula that can be enabled as a conditional formatting rule to draw these lines?

Thank you,
Karen

 

[DushiPunda]

I don't think this is going to be something you can accomplish via conditional formatting. When you say lines, do you mean (cell) borders or do you literally mean lines?

 

[ta7009]

Hello DushiPunda,

Thank you for responding. I don't think this can be accomplished via conditional formatting as i just discovered it. In regards to lines, I mean literally lines. so from a given set of data in range of rows and columns, from center point, i want to draw lines from that center point to determine 90, 180 and 45 degree angles.

I think I need Visual Basic script...

Any help is much appreciated.

Thank you,
Karen

 

[DushiPunda]

Karen,
Agreed regarding the use of VBA. To get you started, you'll want to look at the Shapes.AddConnector Method (link).

The type you're going to want is likely msoConnectorStraight.

Assuming the position is always going to be the same - To make it easier on you, I would suggest recording a macro to get the Begin X/Y & End X/Y values that you'll want for each of your lines. Otherwise you're going to have to do a ton of trial and error in order to get the values you need. In case you don't know, go to the Developer tab at the top of excel, on the left side, right under the Insert tab, is "Record Macro". Click on that button, click OK. Go to the Insert tab at the top, in the Illustrations group, choose Shapes, then choose line. Draw the lines (i would suggest doing one at a time. In between each line, go back to the developer tab and click stop recording, and then click record macro again. this way each of your lines is in it's own macro and will help to reduce confusion)...just a tip regarding drawing the lines...if you hold the shift key, it will make the line either a perfect 180 degree angle or a perfect 90 degree angle, depending upon the angle of the line when you press the shift key.

Once you get your lines drawn and your macros recorded, press ALT + F11. On the left side, you should see "VBAProject (PERSONAL.XLSB)", expand that, then expand modules, and double click on Module1 to look at the recorded macros.

From there you can implement into a module/macro within your workbook and you can come up with the logic of when to show what lines, when to remove (delete) the lines, etc.

I don't know if I just went way over the top for you in explaining this...I have no idea how much you know about VBA, so I decided to just explain as much as I could.

 

[ta7009]

Hi DushiPunda,

Can I reuse the recorded macro on other sheets? I want to be have these lines (45, 95, 180 degree) on various sheets and new sheets going forward. So, I'd like to give a center cell as a starting point and use this center cell as an input parameter. From this center cell, draw the degree lines.

Will macro help me do this?

Karen

 

[DushiPunda]

Karen,
Provided the "center cell" is the same on each sheet, you'll be able to reuse them. For example, I recorded a macro and it gave me this as the result:

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 350.25, 114.75, 350.25, 291.75).Select

So, you can change "ActiveSheet" to any worksheet, like so:

Worksheets("Sheet1").Shapes.AddConnector(msoConnectorStraight, 350.25, 114.75, 350.25, 291.75).Select

This will create the same exact shape in the same exact position on any worksheet, the only thing you'd need to change is "Sheet1" to whatever the sheet name is. If the center cell is not the same on each sheet, then you'll need to create macros for each sheet/line.

 

[ta7009]

It's really odd...

I've been recording each line and adding it to a single Macro so all lines can be added from a single macro:

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 124.9411811024, _
208.2352755906, 449.6470866142, 208.9411811024).Select

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 288.3529133858, _
115.4117322835, 289.0588188976, 304.2352755906).Select
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 288.3529133858, _
115.4117322835, 289.0588188976, 304.2352755906).Select
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 125.6470866142, _
115.7647244094, 451.4117322835, 304.5882677165).Select

However, the last diagonal line going from lower left to top right, when i record it, it's recording as a diagonal line top left to lower right. I can't get this diagonal line to record from lower left to top right...

 

[DushiPunda]

I agree...very interesting...I experienced the same thing. You can just manually swap the BeginX and EndX values like so:

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 451.4117322835, _
115.7647244094, 125.6470866142, 304.5882677165).Select

 

[ta7009]

That worked! Thank you...
So if the square of data where i'm drawing lines get bigger, i assume the lines won't extend to the extended areas?

 

[ta7009]

Can you run a macro to color shade or highlight the 180 and 90 degree cells?