fwawergurl16
08-27-2007, 12:53 AM
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
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