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

 

[Legacy 1363]

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;

<pre/>
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

</pre>

 

[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