Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Change "borders" through VBA code

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi group,

    I'm trying to with a commmandbutton
    search cells Q6:Q1000 and find the cells that are formatted as accounting with the $ symbol
    and change the "borders" to Selection.Borders
    (xlEdgeBotton).Linestyle = xlNone

    Any help would be appreciated
    James

  2. #2

    Join Date
    Mar 2002
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try this ;-

    Dim rng1 As Range, rng2 As Range
    Application.ScreenUpdating = False
    Columns("Q:Q").Insert
    Set rng1 = [Q6:Q1000]
    rng1.FormulaR1C1 = "=IF(CELL(""format"",RC[1])=""C2"",1,"""")"
    On Error GoTo e
    Set rng2 = rng1.SpecialCells(xlCellTypeFormulas, 1)
    With rng2.Offset(0, 1)
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    e:
    On Error GoTo 0
    Columns("Q:Q").Delete


    cojones


    [ This Message was edited by: C. O. Jones on 2002-03-31 18:39 ]

    [ This Message was edited by: C. O. Jones on 2002-03-31 18:39 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-31 17:28, James wrote:
    Hi group,

    I'm trying to with a commmandbutton
    search cells Q6:Q1000 and find the cells that are formatted as accounting with the $ symbol
    and change the "borders" to Selection.Borders
    (xlEdgeBotton).Linestyle = xlNone

    Any help would be appreciated
    James
    James try something like this;


    Sub Macro1()
    Dim MyRg As Range
    Dim oCell As Range

    Set MyRg = Range("Q6:Q1000")
    On Error Resume Next
    Set MyRg = Application.Union(MyRg.SpecialCells(xlCellTypeFormulas, 1), _
    MyRg.SpecialCells(xlCellTypeConstants, 1))
    If Err Then MsgBox "No Data Cells": End

    For Each oCell In MyRg
    If oCell.Style = "Currency" Then
    With oCell.Borders(9)
    .LineStyle = xlNone
    End With
    End If
    Next

    Set MyRg = Nothing

    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-31 18:30, C. O. Jones wrote:
    try this ;-

    cojones
    C O Jones

    Sorry your post was ahead of mine
    didn't mean to post over it...

    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the code Ivan, it works great!
    James

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •