[ashleywanless]

Hi,

Does anyone know the code for a macro to put borders around any cell with data in it? this needs to be the border which cover's the edge of every cell.

There are three sheets i need this for each sheet has data from column a to column N.

If you require anymore info please do ask.

Thanks

Ashley

[Jubbaloo]

I think the easiest way to achieve this is using conditional formatting - you can create a rule that has outside border on cell if not blank.

If 2007 the Highlight the cells you want affected (probably have to do it once per sheet)

Conditional Formatting (On the Home Tab) > New Rule > Format only cells that contain > Change the 'Cell Value' to 'No Blanks' > Click on Format > Borders > Outside Borders

This should achieve what you want.

Let me know if you are using a different version and/or it doesn't make sense,

Cheers

[ashleywanless]

Hi i have already set up the sheet to use 3 conditional formatting options for a RAG (red amber gree) status check. Can this be done using VB worksheet code? Any other suggestions?

Thanks

[bomb #21]

In this case, just use the macro recorder.

Start the recorder, enter a "useful" name, click OK.

Press F5, click Special, select "Constants", click OK.

Select the "All borders" border option, click OK.

Click Stop Recording.

(then record another one for cells with formulas if you need to)

[ashleywanless]

Hi bomb,

appreciate the help. This will allow me to have boarders only around cells with data in? im working on xl2003

Thanks

[Jubbaloo]

Sorry im only just getting back to you

Sub Borders()

' Borders Macro

    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    
    
End Sub

[ashleywanless]

thats cool is this just the code for border or does it include the code required to ensure borders only appear in cells with data in them?

thanks

[Jubbaloo]

It does it all, it selects cells with data and then applies the border to them

[ashleywanless]

Hi,

Works fine excaept the line of code .TintAndShade = 0 causes a debug error, when i remove this line though in all of the places it appears it then works fine.

I do have one issue with this though, the code seems to draw a border around all data. For example i have data in A34 and A35, it draws a border around both these cells (an outside border), i really require the all borders type, is this possible? What part of the code do i need to change?

Thanks you apart from that issue its perfect.

[Excel-Hocam]

Hi Ashley,

you can simply modify the last two lines of Jubbaloo's beautiful macro by replacing "xlNone" by "xlContinuous". You should get exactly what you requested.

Kind regards,

[ashleywanless]

Thank you all i will now close the thread.