Format Row based on Cell Content

[SFrongillo_Lib]

I have several columns/rows with data. I would like a macro to go and
find the cells that contain the word "Total" and select the row and
format it (bold, background color = 37)

For example:

A B C D
row1 10 10 2.7 3.5
row2 11 Total 2.7 3.5
row3 12 12 total 4.5

The word "Total" may appear in any column or any row within the data
range. Some rows may not have the word total.

I'd like the macro to find the word "Total" and then highlight from
Column A through the end of the row and format it.

I have a macro that will bold from the word "Total to the end of row."
Here's the code for that:

Sub BoldTotals()
' Macro finds all cells containing the word TOTAL and applies
' Bold, underline and shading to the row
Dim LastColumn As Integer

LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

For Each C In ActiveSheet.UsedRange
If C Like "*TOTAL*" Or C Like "*Total*" Then
' select from current cell to end of range
Range(C, C.Offset(0, (LastColumn - C.Column))).Activate

' set those cells to bold
With Selection
.Font.Bold = True
.Interior.ColorIndex = 37
.Borders(xlTop).Weight = xlThin
.Borders(xlBottom).Weight = xlMedium
End With
End If
Next
Application.ScreenUpdating = True
End Sub


Rather than selecting from the word Total to the right (end of row),
I'd like to highlight and format the whole row.

Any suggestions? This was a macro given to me and I'm not that good in
scripting these. Thanks for any help!

 

[Bob Phillips]

Try this

Dim cRows As Long
Dim cCols As Long
Dim i As Long, j As Long

With ActiveSheet
cRows = .UsedRange.Rows.Count
cCols = .UsedRange.Columns.Count
For i = 1 To cRows
For j = 1 To cCols
With .UsedRange
If LCase(.Cells(i, j).Value) = "total" Then
With .Range(.Cells(i, 1), .Cells(i, cCols))
.Interior.ColorIndex = 15
.Borders(xlTop).Weight = xlThin
.Borders(xlBottom).Weight = xlMedium
End With
Exit For
End If
End With
Next j
Next i
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[keepitcool]

Note1: the option compare is needed for like operator, and should go in
the TOP of the module's code=> thus applies to all code in module.

Note2: the usedrange may not start in ColumnA or Row1, and continues as
far as the last cell with formatting and/or data


Option Explicit
Option Compare Text

Sub BoldTotals()
' Macro finds all cells containing the word TOTAL and applies
' Bold, underline and shading to the row
Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet
For Each c In .UsedRange.Cells
If c.Text Like "*total*" Then
With Intersect(.UsedRange, c.EntireRow)
.Font.Bold = True
.Interior.ColorIndex = 37
.Borders(xlTop).Weight = xlThin
.Borders(xlBottom).Weight = xlMedium
End With
End If
Next
End With
Application.ScreenUpdating = True
End Sub

cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[SFrongillo_Lib]

Thanks! It worked!! This is going to make my life a little easier!!