Macro to highlight a specified row

[Jodie]

How would I write a macro to find "Gauranteed" in any cell in Column "B" and
then highlight that row? Also, this would be for an entire workbook.

 

[Rick Rothstein]

I spelled it the way you did for the code below, but I want to point out
that you spelled "gauranteed" incorrectly (it's "ua" not "au" for the second
and third letters)...

Sub FindGAURANTEED()
Dim WS As Worksheet, R As Range
For Each WS In Worksheets
Set R = WS.Columns("B").Find("GAURANTEED", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not R Is Nothing Then
WS.Activate
R.EntireRow.Select
Exit Sub
End If
Next
End Sub

 

[Matthew Dyer]

How would I write a macro to find "Gauranteed" in any cell in Column "B" and
then highlight that row?  Also, this would be for an entire workbook.

Assuming column A has all the 'main' data and has no spaces between
data and there is no header row:

sub highlight()
for i = 1 to cells(rows.count, "A").end(xlup).row
if cells(i, "B").value = "Guaranteed" then
Cells(i, 1).Resize(, 2).Interior.ColorIndex = 36
next i
end sub

If there is a header row you just need to change i = 1 to i = 2 so it
starts on the second row instead of the first. If your data is not
'grouped' and there are empty rows between your data, this loop will
stop at the first empty space in column A.

In the passage .Resize(, 2), change the 2 to however many cells to the
right you want highlighted. Currently it is set as 2 so only columns A
and B in the row will be highlighted. Changing it to 5 would highlight
columns A-E, 10 would highlight columns A-J, etc...

Also, you spelled guaranteed wrong in your post. I used the correct
spelling in my coding

This sub would work only on the active sheet, not the entire workbook.
If there are multiple sheets you want this to work on, I'd copy/past
this into a sepearte workbook module with a 'shortcut' key (ctrl+Q is
what I usually use). With the 'macro' workbook in the background and
the sheet you want to modify active you can press your shortcut key
and vioala, rows that meet the criteria will be highlighted.

 

[Per Jessen]

Hi Jodie

Assuming you have headers in row 1, we can use autofilter like this:

Sub Highlight()
Dim sh As Worksheet
On Error Resume Next
For Each sh In ThisWorkbook.Sheets
With sh
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("B1:B" & LastRow).AutoFilter field:=1,
Criteria1:="Gauranteed"
If Err.Number > 0 Then
Err.Clear
Else
.Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).
_
EntireRow.Interior.ColorIndex = 6
.Columns("B").AutoFilter
End If
End With
Next
End Sub

Regards,
Per

 

[Ryan H]

I wasn't sure what you meant by "highlight". Did you want to select it or
color it yellow? So I did both in the code below. Hope this helps! If so,
click "YES" below.

Sub HighlightRow()

Dim MyRow As Long

MyRow = Range("B:B").Find(What:="Gauranteed", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row

' select the row
Rows(MyRow).Select

' highlight row yellow
Rows(MyRow).Interior.Color = 65535

End Sub

 

[Jodie]

Thank you all, Gentlemen. These all look as though they will work. I will
try each of them to see which works best for my project.

If any of you are up for another one, I also need to write a macro which
will subtract the amount in column N of the row which contains "Guaranteed"
in column B, from the amount that is in column N of the row which contains
"Total" in column A. For example, I have spreadsheets that have fund names
in column B. In Column N there is a total dollar amount invested in the
corresponding fund. I need to back out the amount of funds in the Guaranteed
from the total of all funds. Each spreadsheet may have different funds and
some may not have Guaranteed at all. I would like the results to populate in
column N, directly under the Total row. Can any of you help?