Change "borders" through VBA code

[James]

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

[C. O. Jones]

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 ]

[Ivan F Moala]

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

[Ivan F Moala]

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...

[James]

Thanks for the code Ivan, it works great!
James