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]

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?

That's correct. The line sizes are hard-coded, so they will not auto-expand (or shrink) based on the size of your range.

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

Can you explain what exactly you mean? On a new, blank worksheet, if I create the three lines you made, I see that the vertical line is in between columns F & G. The horizontal line is in between rows 14 and 15. Would you want to highlight all of the cells that are touching those two lines? Assuming you're using a worksheet that has default column widths and row heights:

1. F9:G20 (the cells on either side of the vertical line)
2. D14:I15 (the cells on either side of the horizontal line)

 

[DushiPunda]

Based on the thread you commented on earlier, I think it would be possible to look at the size of your range of data and adjust the points accordingly. Doing some testing now. Can you provide me a sample of your data please?

 

[ta7009]

I want to highlight the cells in which the horizontal and vertical lines are drawn.

 

[DushiPunda]

I want to highlight the cells in which the horizontal and vertical lines are drawn.

Based on what I said before, if you're doing this on an unedited sheet (with default row heights/column widths), then the vertical and horizontal lines are on the border of two columns and two rows, respectively.

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]V
[/TD]
[TD="align: center"]V
[/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"]V
[/TD]
[TD="align: center"]V[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]^
[/TD]
[TD="align: center"]^
[/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"]^
[/TD]
[TD="align: center"]^
[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]>
[/TD]
[TD="align: center"]<
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

So, illustrated here as best I can, the arrows are pointing to where the lines are created for me. Like I said above, they are in between two rows/two columns.

Will the dimensions of your "square" of data always be an even amount of rows and columns? I obviously don't have any of your data in front of me, but based on the size of the lines you created, I'm assuming that the "square" starts in cell D9 and ends in cell I20 (which is 6 Columns & 12 Rows).

 

[ta7009]

Vertical line is centered on L column and horizontal line is centered on row 15.

 

[DushiPunda]

Vertical line is centered on L column and horizontal line is centered on row 15.

Okay, can you tell me what the width of your columns and the height of your rows is please? And provide me with a sample of your data.

 

[ta7009]

can i upload an image here?
Row height: 14.4
Column width: 3.89

 

[DushiPunda]

I'm not sure if you can, but you can upload it to an image hosting site, like Imgur: The most awesome images on the Internet and post the link here. I likely won't be able to access it until I get off of work.