Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Select Multiple Non Adjacent Rows based on a cell value? | Excel Worksheet Functions | |||
Filter/Select multiple rows | Excel Discussion (Misc queries) | |||
Select rows of data in a worksheet on one criteria in multiple co | Excel Worksheet Functions | |||
How do I select multiple rows randomly in MS Excel? | Excel Discussion (Misc queries) | |||
Select all data, multiple rows | Excel Discussion (Misc queries) |