Sleeper: Easy Cell Reference - "Guide" Lines

[wezred]

I need a software or even maybe a macro that can help create guide lines in the excel worksheet much like the picture below. Basically, I'm having trouble trying to see if I'm keying in my figures in the right cells especially when the worksheet is huge and the zoom is at 75% or lower. Could you let me know if this is possible? A straight vertical and horizontal arrow would be an alternative if it's possible.

[MWE]

I need a software or even maybe a macro that can help create guide lines in the excel worksheet much like the picture below. Basically, I'm having trouble trying to see if I'm keying in my figures in the right cells especially when the worksheet is huge and the zoom is at 75% or lower. Could you let me know if this is possible? A straight vertical and horizontal arrow would be an alternative if it's possible.

If I understand what you want to do, this could be done with either cell borders or with transparent text boxes.

Using cell borders, select one of the "quarters", then right click and navigate to Format Cells and then to Borders.

Using text boxes, you simpley select a text box from the drawing tools toolbox, position the cursor on one corder, click and drag to the other corner.

Of course, this could be done with a macro. Using the macro recorder for either method will generate VBA code that should get you started:

1. Tools | Macro | Record New Macro
2. click OK to record macro in active workbook
3. perform manual operations
4. click on the stop recording button on the Macro Recorder window
5. Tools | Macro | Visual Basic Editor (or Alt+F11)
6. New macro is in a code module for the active project most likely called Module1

Using the Macro Recorder is a very good way to learn about macros and VBA.

[wezred]

Thanks for the tip but i don't think it's what i need. You're close though.

However, I'd like the "guide" lines to move when i move my active cell every time without affecting my formatting (like borders and stuff). Perhaps a solution would be using arrows in drawing but do you know how to program a macro to move the arrows such that it'll always point from the side/edge of the worksheet (reference line) to my active cell everytime i move it?

[TonyJollans]

Hi wezred,

AFAIk, the only way to do what you ask involves applying temporary formats to shading and/or borders - if you don't want that (because of the possibility of losing your original formats) then you could do your second choice of moving an arrow about.

Create a drawing of an arrow on your sheet - note its name (mine is called "Line 1") and then add this code to the sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Shapes("Line 1").DrawingObject.Top = Target.Top + Target.Height
    Me.Shapes("Line 1").DrawingObject.Left = Target.Left + Target.Width
End Sub

My drawing is of a line going from bottom right to an arrowhead at top left - if you design it differently you might need slightly different code. There are lots of possible variations on the theme but that should at least give you something to work with.