Solved

Conditionally draw border with VBA

Posted on 2013-06-20
2
399 Views
Last Modified: 2013-06-21
Have an excel question (version 2010) as follows. For example, 2 columns and 7 rows

         A     B
1      Key  Value
2      D1   10
3              20
4      E1    11
5              21
6              31
7      F1    22
would like to draw the border (all solid lines around the cells, only draw the bottom lines as an example), but if there are no values in column A, do not draw anything, simply take the defaults.

         A     B
1      Key  Value
    --------------------
2     D1   10
3             20
    --------------------
4     E1    11
5             21
6             31
    --------------------
7     F1   22
    --------------------

Wrote something like this

Sub T()

With Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
End Sub

How to add the check cell condition statement?
0
Comment
Question by:jl66
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39264874
You might try a macro like this:
Sub T()
Dim rw As Range
With Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    With .Borders
        .LineStyle = xlOutline
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    For Each rw In .Rows
        If rw.Cells(2, 1).Value <> "" Then
            With rw.Borders(xlEdgeBottom)
                .LineStyle = xlDash
            End With
        End If
    Next
End With
End Sub

Open in new window

Brad
0
 

Author Closing Comment

by:jl66
ID: 39265713
Thanks a lot.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question