Find and Format code doesn't work
 

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

Find and Format code doesn't work
 

The first problem is that this isn't valid:

with rng(i, "f:m")
You could use:
with cells(i, "F").resize(1, 8)

The cell in row i and column F resized to 1 row by 8 columns (F:M).

I didn't look any further.

Gwen wrote:

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

--

Dave Peterson

Find and Format code doesn't work
 

This will get you to the columns on the same row as the found cell.

If Not Rng Is Nothing Then
x = Rng.Address
With Ragne("F" & x.Row & ":M" & x.Row)
.Font.Bold = true
With
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End With

"Gwen" wrote:

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

Find and Format code doesn't work
 

I think Dave misread the usage of "I" since it it not a row identifier.
There were several logic problems with your code. There is no reason to
create a single element array containing "Total". Just search for that
string. This is tested and worked for me.

I wasn't sure what you wanted as xlAutomatic - it is only appropriate for
the Font.

Look at the settings and see if that is what you want:

Sub AAA()
Dim myArr As String
Dim Rng As Range
Dim sAddr As String
Application.ScreenUpdating = False
myArr = "total"

Set Rng = Range("c:c").Find(What:=myArr, _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
sAddr = Rng.Address
Do
With Cells(Rng.Row, "F").Resize(1, 8)
.Font.Bold = True
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End With
Set Rng = Range("C:C").FindNext(Rng)
Loop While Rng.Address < sAddr
End If
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Gwen" wrote:

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

Find and Format code doesn't work
 

Perfect!
Thank you very much.
Thank all of you for taking time out to help me.

"Tom Ogilvy" wrote:

I think Dave misread the usage of "I" since it it not a row identifier.
There were several logic problems with your code. There is no reason to
create a single element array containing "Total". Just search for that
string. This is tested and worked for me.

I wasn't sure what you wanted as xlAutomatic - it is only appropriate for
the Font.

Look at the settings and see if that is what you want:

Sub AAA()
Dim myArr As String
Dim Rng As Range
Dim sAddr As String
Application.ScreenUpdating = False
myArr = "total"

Set Rng = Range("c:c").Find(What:=myArr, _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
sAddr = Rng.Address
Do
With Cells(Rng.Row, "F").Resize(1, 8)
.Font.Bold = True
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End With
Set Rng = Range("C:C").FindNext(Rng)
Loop While Rng.Address < sAddr
End If
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Gwen" wrote:

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

Find and Format code doesn't work
 

Since x is a string containing the address of rng, I think you will have
problems with x.row. Why not just skip X and use rng.row instead of x.row.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote:

This will get you to the columns on the same row as the found cell.

If Not Rng Is Nothing Then
x = Rng.Address
With Ragne("F" & x.Row & ":M" & x.Row)
.Font.Bold = true
With
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End With

"Gwen" wrote:

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

Find and Format code doesn't work
 

Thanks, Tom.

I did misread that line of code.

And I think that the OP may have taken some code that I posted. It was
originally written to look for a few words. And I just removed all the other
elements of the array.

So that's my fault, too <bg.

Tom Ogilvy wrote:

I think Dave misread the usage of "I" since it it not a row identifier.
There were several logic problems with your code. There is no reason to
create a single element array containing "Total". Just search for that
string. This is tested and worked for me.

I wasn't sure what you wanted as xlAutomatic - it is only appropriate for
the Font.

Look at the settings and see if that is what you want:

Sub AAA()
Dim myArr As String
Dim Rng As Range
Dim sAddr As String
Application.ScreenUpdating = False
myArr = "total"

Set Rng = Range("c:c").Find(What:=myArr, _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
sAddr = Rng.Address
Do
With Cells(Rng.Row, "F").Resize(1, 8)
.Font.Bold = True
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End With
Set Rng = Range("C:C").FindNext(Rng)
Loop While Rng.Address < sAddr
End If
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Gwen" wrote:

I want to format the "total columns.
I would like this code to look for the rows that has "total.
When found is true format columns "f:m"

Logic: For each row that has the word "total" then format columns f:m
When I used this code to delete rows it worked perfectly, so there must be
something wrong with these lines:

with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with

Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("total")

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("c:c").Find(What:=myArr(I), _
After:=Range("c" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
with rng(i, "f:m")
.font.bold = true
.Borders (xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.Rows.ColorIndex = xlAutomatic
end with
'Rng.EntireRow.Delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True

Thanks

--

Dave Peterson