Looking for a better way - Excel VBA 1

[rss01]

I currently have the following code that loops thru a hard coded range of rows, looking for a sub-total formula that meets my criteria. Once I find a cell that meets that I change the cells color and border.

Is there a way that I can set this dynamically instead of hard coding the range of rows?

I’m running this code from Access to format my excel sheet.

For intJ = 2 To 2000
    For Each strJ In Array("H")
    If Left(xlsWorksheet.Range(strJ & intJ).Formula, 9) = "=SUBTOTAL" Then
    xlsWorksheet.Rows(intJ).Font.Bold = True
    For Each strCol In Array("H")
    xlsWorksheet.Range(strCol & intJ).Interior.ColorIndex = 44
    xlsWorksheet.Range(strCol & intJ).Borders(xlEdgeLeft).LineStyle = xlContinuous
    xlsWorksheet.Range(strCol & intJ).Font.Bold = True
    xlsWorksheet.Range(strCol & intJ).Borders(xlEdgeRight).LineStyle = xlContinuous
    xlsWorksheet.Range(strCol & intJ).Borders(xlEdgeTop).LineStyle = xlContinuous
    xlsWorksheet.Range(strCol & intJ).Borders(xlEdgeBottom).LineStyle = xlContinuous
  
    Next strCol
    End If
    Next strJ
    Next intJ

I want to replace

For intJ = 2 To 2000

 

[SkipVought]

Hi,

Easiest way is to use Conditional Formatting in the Formula is textbox
[tt]
=RIGHT($A1,5)="Total"
[/tt]
If you insist on writing code instead

with activesheet.usedrange
  r1 = .row
  r2 = r1 + .rows.count - 1
end with
for intJ = r1 + 1 to r2

next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rss01]

Skip,

Thanks you bailed me out again.

I used the conditional formatting.

xlsWorksheet.Columns.Range("H:H").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RIGHT($C1,5)=""Total"""
        
    With xlsWorksheet.Columns.Range("H:H").FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlsWorksheet.Columns.Range("H:H").FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlsWorksheet.Columns.Range("H:H").FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlsWorksheet.Columns.Range("H:H").FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    
    xlsWorksheet.Cells.Range("H:H").FormatConditions(1).Font.Bold = True
    xlsWorksheet.Cells.Range("H:H").FormatConditions(1).Interior.ColorIndex = 44