Solved: how to detect white/blank space in a row?

[fwawergurl16]

Hi. I'm an intern with a media company. I'm asked to do macros as an assignment & this is Phase 3 of the assignment.

In the spreadsheet that im to clean & format, im suppose to bold certain rows of words. I've done all the formatting already, except for the bolding of words part. Of the many many rows in the sheet, the only rows that needs to be bolded do not have a blank space in front of it (no indentation sorta thing). Is there any way to detect those sort of rows of words so that it could be bolded?

Attached is the sample of the sheets. Does anyone know how to get such a thing done? All help is much appreciated!

- fwawergurl16 :help

p/s: I've highlighted a row of what I meant in the sample document (the intended code shall be looped)

[geekgirlau]

Sub FormatBoldRows()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~
' Dependancies: "shExpenditure" is the name given to the sheet where we are
' performing this update
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~
Dim rng As Range
Dim lngRowLast As Long


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

For Each rng In shExpenditure.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 Then
rng.EntireRow.Font.Bold = True
End If
End If
Next rng
End Sub

[mikerickson]

How about conditional formatting with the custom formula

=(Left(A1,1)<>" ")?

[fwawergurl16]

geekgirlau,

i tried the codes but there's an error '424' Object Required in this line:

lngRowLast = WorksheetFunction.Match("Grand Total", _
shExpenditure.Range("A:A"), False)
I tried changing the name of my worksheet to shExpenditure and even tried changing the shExpenditure name to my own worksheet name. How can this be corrected? I can't detect anything wrong *sigh* :(

[geekgirlau]

Are you changing the name of the sheet in the VBE window? Go into your code and make sure the Properties Window is displayed (View, Properties Window). Then click on the sheet and change the name of the sheet in the Properties Window.

[fwawergurl16]

Are you changing the name of the sheet in the VBE window? Go into your code and make sure the Properties Window is displayed (View, Properties Window). Then click on the sheet and change the name of the sheet in the Properties Window.
Brilliant! Did the trick. Stupid me. Took such a long time to get one small thing done. Thank you SO VERY much!

- fwawergurl16

last q: Are there other ways to ensure that the name changes automatically instead of going into the VB Editor? Cos it could be quite troublesome for users of the macro who knows nothing 'bout VBE.

[geekgirlau]

You don't need the users to change the name. The idea is that in the VBE window the name doesn't change, whilst the tab name can be changed at any time. If you use this method, your macro will always run regardless of the name displayed on the tab.

[fwawergurl16]

You don't need the users to change the name. The idea is that in the VBE window the name doesn't change, whilst the tab name can be changed at any time. If you use this method, your macro will always run regardless of the name displayed on the tab.

Thank u once again. Thanks for sharing your knowledge with me. I actually have no prior knowledge of VBA til I was given such tasks :) Thanks!