Formatting in VBA

[dunk]

In an excel spreadsheet I'm looking to find a way to search the entire spreadsheet, find the "Dept Total" cells, go to the cell to the right of it(this contains a currency value)and put a top border line above this cells contents. This will make a visible split between lots of Dept Totals.

Any help would be very welcome

 

[Ivan F Moala]

Hi Dunk

here is one way to do this;

Sub finder()
Dim SearchString As String
Dim Sh, F, Location As String, Flag, Found1st

SearchString = "Dept Total"
Set Sh = Sheets.Application

For Each Sh In Application.Sheets
Flag = 0
With Sh.Range("A1:IV65536")
Do
Set F = .Find(SearchString, lookat:=xlWhole, after:=ActiveCell, LookIn:=xlValues)
If Not F Is Nothing Then
If Flag = 0 Then Found1st = F.Offset(0, 1).Address(external:=True)
Location = F.Address
Sh.Select
Range(Location).Offset(0, 1).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Flag = Flag + 1
End If
Loop Until Found1st = ActiveCell.Address(external:=True) And Flag > 2 Or F Is Nothing
End With
Next Sh

End Sub


HTH

Ivan

 

[dunk]

This works great!!
thanks very much

 

[dunk]

I'm now looking to modify the above code so that it formats the next nine cells in the same row, and not just the next cell.

Any help appreciated

dunk

 

[dunk]

Any help would be greatly appreciated.