Find and Format column A and B then rows

sburrill

New Member
Joined
Aug 23, 2005
Messages
8
I have several spreadsheets with text in Column A and data or text in columns B through T.

Where text in column A contains "TOTAL" I want to format that row bold.

Where text in column B contains "Index" I want to format that row italic.

Apply this to all worksheets (over 70) in the workbook.

Thanks for your leads advice.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hiya, you can start with this and go from there. Hope it helps.

Code:
Sub totalbold()
'this makes the row bold upon finding "total"
Sheets("sheet1").Select
Dim LastRow
LastRow = Columns(1).Find(What:="TOTAL", After:=[A1], LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
Range(Cells(LastRow, "A"), Cells(LastRow, "T")).Select
Selection.Font.bold = True
End Sub
It will find the word total (must be in column A) and make cells A to T bold
You should be able to adapt the above to look for index and make italic.
If anyone can advise how to go about running this across every worksheet, then feel free to advise.
Regards
Mark
 
Upvote 0
oh I just realised that will stop after finding the first "TOTAL" and not continue on to the next.

can anyone else please advise :)
 
Upvote 0
This could easily be done with conditional formatting.

Select all relevant rows, goto Format>Conditional Formatting..., select formula is and enter this

=$A1="TOTAL"

and format as required.

For column B add another condition and use this formula

=$B1="Index"

If you want to apply this to many sheets as indicated I would recommend that you record a macro (Tools>Macro...Record New Macro...) when you do it manually

The code generated can be adpated to run over multiple worksheets/books.
 
Upvote 0

Forum statistics

Threads
1,223,165
Messages
6,170,464
Members
452,329
Latest member
Irefsports

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top