select multiple rows and format
 

Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
Dim MyRange As Range
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
Set MyRange = Range("E1:E" & lastrow)
For Each c In MyRange
If InStr(1, UCase(c), "COLLEGE") Then
c.EntireRow.Font.Bold = True
End If
Next
End Sub

Mike

"sdg8481" wrote:

Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

Sub FindAndDeleteRow()

Dim FoundCell As Range
Dim FirstAddress As String
Dim delRng As Range

With Worksheets("sheet2").Range("a1:a25")
Set FoundCell = .Cells.Find(what:="college", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

If FoundCell Is Nothing Then
Else
FirstAddress = FoundCell.Address
Do
If delRng Is Nothing Then
Set delRng = FoundCell
Else
Set delRng = Union(delRng, FoundCell)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

If delRng Is Nothing Then

Else
delRng.EntireRow.Delete
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sdg8481" wrote in message
...
Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing
out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

Don,

I think the OP may be a bit cross if he deletes the rows he was trying to
format as bold:)

perhaps you meant this

delRng.EntireRow.Font.Bold = True

Mike

"Don Guillett" wrote:

Sub FindAndDeleteRow()

Dim FoundCell As Range
Dim FirstAddress As String
Dim delRng As Range

With Worksheets("sheet2").Range("a1:a25")
Set FoundCell = .Cells.Find(what:="college", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

If FoundCell Is Nothing Then
Else
FirstAddress = FoundCell.Address
Do
If delRng Is Nothing Then
Set delRng = FoundCell
Else
Set delRng = Union(delRng, FoundCell)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

If delRng Is Nothing Then

Else
delRng.EntireRow.Delete
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sdg8481" wrote in message
...
Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing
out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

I presented code to delete. to bold is easier
Sub FindAndBold()
With Worksheets(2).Range("a1:a50")

Set C = .Cells.Find(what:="college", _
after:=.Cells(1, 1), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.Font.Bold = True
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sub FindAndDeleteRow()

Dim FoundCell As Range
Dim FirstAddress As String
Dim delRng As Range

With Worksheets("sheet2").Range("a1:a25")
Set FoundCell = .Cells.Find(what:="college", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

If FoundCell Is Nothing Then
Else
FirstAddress = FoundCell.Address
Do
If delRng Is Nothing Then
Set delRng = FoundCell
Else
Set delRng = Union(delRng, FoundCell)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

If delRng Is Nothing Then

Else
delRng.EntireRow.Delete
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sdg8481" wrote in message
...
Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing
out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty
rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

THANKS GUYS...Works perfect

"Mike H" wrote:

Don,

I think the OP may be a bit cross if he deletes the rows he was trying to
format as bold:)

perhaps you meant this

delRng.EntireRow.Font.Bold = True

Mike

"Don Guillett" wrote:

Sub FindAndDeleteRow()

Dim FoundCell As Range
Dim FirstAddress As String
Dim delRng As Range

With Worksheets("sheet2").Range("a1:a25")
Set FoundCell = .Cells.Find(what:="college", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

If FoundCell Is Nothing Then
Else
FirstAddress = FoundCell.Address
Do
If delRng Is Nothing Then
Set delRng = FoundCell
Else
Set delRng = Union(delRng, FoundCell)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

If delRng Is Nothing Then

Else
delRng.EntireRow.Delete
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sdg8481" wrote in message
...
Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing
out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

Mike, I didn't read right the first time. Getting old, you know.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Don,

I think the OP may be a bit cross if he deletes the rows he was trying to
format as bold:)

perhaps you meant this

delRng.EntireRow.Font.Bold = True

Mike

"Don Guillett" wrote:

Sub FindAndDeleteRow()

Dim FoundCell As Range
Dim FirstAddress As String
Dim delRng As Range

With Worksheets("sheet2").Range("a1:a25")
Set FoundCell = .Cells.Find(what:="college", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

If FoundCell Is Nothing Then
Else
FirstAddress = FoundCell.Address
Do
If delRng Is Nothing Then
Set delRng = FoundCell
Else
Set delRng = Union(delRng, FoundCell)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

If delRng Is Nothing Then

Else
delRng.EntireRow.Delete
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sdg8481" wrote in message
...
Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row
if
the word "College" exists somewhere in column E. It maybe worth
pointing
out
that this word may be part of a phrase so not sure if wildcard
characters
will be needed.

If tried the usual and ideas found on this site, however i can't use
the
simple filter options because my spreadsheet contains multiple empty
rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

select multiple rows and format
 

Another option would be to use format|Conditional formatting (xl2003 menus).

Select the range (a1:X999, say)

And with row 1 containing the activecell:

Format|Conditional|formatting
formula is:
=countif($e1,"*college*")0
and use Bold in the formatting dialog.

sdg8481 wrote:

Hi,

I have a fairly simple request, but i just just can't seems to find a
solution, hope one of you can help...

Basically, i need a VBA code that will bold the text of the entire row if
the word "College" exists somewhere in column E. It maybe worth pointing out
that this word may be part of a phrase so not sure if wildcard characters
will be needed.

If tried the usual and ideas found on this site, however i can't use the
simple filter options because my spreadsheet contains multiple empty rows,
therefore i think VBA id the way to go.

Hope this makes sense and that you can help.

--

Dave Peterson