Solved: help for selective highlight of rows

[fwawergurl16]

Need some help here. How can I highlight the rows of data that do not have spacing AND contains the word total in column A?

I've this coding originally for bolding the rows that does not contain a blank space in its column A. I've modified the codes, in hopes to get it to do the highlighting task, but to no avail...probably i missed out something, or this isn't the way of writing the codes.

Can anyone help me out here? I've included a sample sheet "test1" for a clearer picture of things (codes in 'otherUtilities_2'). Thanks!


Sub colorTotal_mm()

'name of sheet in VBE is Sheet2
'all texts begin from A7 onwards
'rows that shall be highlighted ends with 'Total'
'color for highlight is 35

Dim rng As Range
Dim lngRowLast As Long
Dim StrWs As String

' detect last row (don't go any further than "Grand Total")
lngRowLast = WorksheetFunction.Match("Grand Total", _
Sheet2.Range("A:A"), False)

For Each rng In Sheet2.Range("A7:A" & lngRowLast)
' skip blank rows
If rng.Formula <> "" Then
' skip "indented" rows (those with a space)
' 160 is the ASCII code for a space, in case a normal space
' is not detected
If Left(rng.Formula, 1) <> " " And Asc(Left(rng.Formula, 1)) <> 160 And StrWs = "*Total" Then
rng.Interior.ColorIndex = 35
End If
End If
Next rng
End Sub

[Bob Phillips]

Sub colorTotal_mm()

'name of sheet in VBE is Sheet2
'all texts begin from A7 onwards
'rows that shall be highlighted ends with 'Total'
'color for highlight is 35

Dim rng As Range
Dim lngRowLast As Long
Dim StrWs As String

' detect last row (don't go any further than "Grand Total")
lngRowLast = WorksheetFunction.Match("Grand Total", _
Sheet2.Range("A:A"), False)

For Each rng In Sheet2.Range("A7:A" & lngRowLast)
' skip blank rows
If rng Like "*Total*" Then
rng.Interior.ColorIndex = 35
End If
Next rng
End Sub

[fwawergurl16]

Thank u SO much, xld! The only thing is that this code will highlight all rows with the word 'total'...i would need the codes to only highlight the rows with the word 'total' AND those rows must have words that are not indented.

e.g. "Test" - highlight
" Test" - dont highlight

Any way to get it done?

[Charlize]

Maybe add a second conditionIf rng Like "*Total*" And Len(rng) = 5 Thenand you're using
option compare texton top of your module.

[Bob Phillips]

Thank u SO much, xld! The only thing is that this code will highlight all rows with the word 'total'...i would need the codes to only highlight the rows with the word 'total' AND those rows must have words that are not indented.

e.g. "Test" - highlight
" Test" - dont highlight

Any way to get it done?

Why would they have a space?



If rng.Value Like "*Total*" And _
Not (Left$(rng.Value, 1) = " " Or _
Asc(Left(rng.Value, 1)) = 160) Then
Debug.Print rng.Value
End If

[fwawergurl16]

Why would they have a space?



If rng.Value Like "*Total*" And _
Not (Left$(rng.Value, 1) = " " Or _
Asc(Left(rng.Value, 1)) = 160) Then
Debug.Print rng.Value
End If

Hi xld,

The data are exported from a database software by another company. From the raw data itself, certain rows are indented, resulting to the space.

I tried applying the codes that u gave, but there's this error: Run-time error '5' - Invalid procedure or argument. When I modified it & included the rng.Interior.ColorIndex = 35, it does the highlighting, but only for one row, & then the error comes out. How can this be solved? I've included a sample raw data for better understanding. Thanks.


For Each rng In Sheet2.Range("A7:A" & lngRowLast)
' skip blank rows
If rng.Value Like "*Total*" And _
Not (Left$(rng.Value, 1) = " " Or _
Asc(Left(rng.Value, 1)) = 160) Then
'Debug.Print rng.Value
rng.Interior.ColorIndex = 35
End If
Next rng

[geekgirlau]

Sub FormatBoldRows_mm()
Dim rng As Range
Dim lngRowLast As Long


' detect last row (don't go any further than "Grand Total")
lngRowLast = WorksheetFunction.Match("Grand Total", _
MonthAndMedia.Range("A:A"), False)
'testing.Range("A:A"), False)

For Each rng In MonthAndMedia.Range("A7:A" & lngRowLast)
' skip blank rows
If rng.Formula <> "" Then
' skip "indented" rows (those with a space)
' 160 is the ASCII code for a space, in case a normal space
' is not detected
If Left(rng.Formula, 1) <> " " And _
Asc(Left(rng.Formula, 1)) <> 160 And _
rng Like "*Total" Then
rng.EntireRow.Interior.ColorIndex = 35
'rng.EntireRow.Font.Bold = True
End If
End If
Next rng
End Sub

[fwawergurl16]

Thanks, geekgirlau! Works great :)

[geekgirlau]

My pleasure - don't forget to mark the thread as "solved" (use the Thread Tools at the top of the page).