Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Conditionally draw border with VBA

Posted on 2013-06-20
2
Medium Priority
?
431 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

782 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