Change "borders" through VBA code
VBA Telemetry pings you when your VBA projects fail
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

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
  •  

 

 
DMCA.com