Hi. I’d like to write a procedure to search an excel file and find all the cells that have the word “TOTAL” in it and make the word TOTAL bold. I also need to search for the cells that have a number with an “s” next to them, like -6s, 1s, -12s and make them red. Could someone help me with this. Thanks a lot!
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
search/replace numbers and make red (excel xp, w2000)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » search/replace numbers and make red (excel xp, w2000)
- This topic has 8 replies, 3 voices, and was last updated 23 years, 4 months ago.
Viewing 1 reply threadAuthorReplies-
H. Legare Coleman
AskWoody PlusJanuary 31, 2002 at 2:46 pm #567433 -
WSjha900
AskWoody Lounger -
WSSammyB
AskWoody LoungerJanuary 31, 2002 at 5:26 pm #567458
> i need to do this for a whole workbook
Option Explicit Sub BoldTOTAL() Dim s As Worksheet Dim c As Range Const FINDTHIS = "TOTAL" Dim strFirstAddress As String For Each s In Worksheets With s.UsedRange Set c = .Find(What:=FINDTHIS, LookIn:=xlFormulas, _ LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then strFirstAddress = c.Address Do c.Font.Bold = True Set c = .FindNext Loop While c.Address strFirstAddress End If End With Next s End Sub Sub RedS() Dim s As Worksheet Dim c As Range For Each s In Worksheets For Each c In s.UsedRange If c.Value Like "*s" Then If IsNumeric(Left(c.Value, Len(c.Value) - 1)) Then _ c.Font.ColorIndex = 3 End If Next c Next s End Sub
-
H. Legare Coleman
AskWoody PlusJanuary 31, 2002 at 9:29 pm #567506OK, here is how to do it with conditional formatting:
1- Select the first sheet in the workbook.
2- Hold down the shift key and click on the tab for the last sheet in the workbook. This should select all of the sheets in the workbook.
3- Click on the square in the upper left corner of the first sheet to select all of the cells on the sheet (actually on all of the sheets).
4- Select Conditional Formatting from the Format menu.
5- In the first drop down box select Cell Value Is.
6- In the second drop down box select Equal To.
7- In the formula box put: =”TOTAL”
8- Click on the Format… button an select Bold for the font.
9- Click on the ADD>> button at the bottom of the dialog box to get a place to enter a second condition.
10- In the first drop down list select Formula Is.
11- In the formula box enter: =AND(RIGHT(A1,1)=”s”,(LEFT(A1,LEN(A1)-1)*1)=VALUE(LEFT(A1,LEN(A1)-1)))
12- Click on the Format… button.
13- Click on the arrow in the Color box and select Red.
14- Click on OK.
-
-
-
WSSammyB
AskWoody LoungerJanuary 31, 2002 at 4:55 pm #567453Here are a couple of macros that hopefully do what you want:
Option Explicit Sub BoldTOTAL() Dim c As Range Const FINDTHIS = "TOTAL" Dim strFirstAddress As String With ActiveSheet.UsedRange Set c = .Find(What:=FINDTHIS, LookIn:=xlFormulas, _ LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then strFirstAddress = c.Address Do c.Font.Bold = True Set c = .FindNext Loop While c.Address strFirstAddress End If End With End Sub Sub RedS() Dim c As Range For Each c In ActiveSheet.UsedRange If c.Value Like "*s" Then If IsNumeric(Left(c.Value, Len(c.Value) - 1)) Then _ c.Font.ColorIndex = 3 End If Next c End Sub
-
WSjha900
AskWoody LoungerFebruary 1, 2002 at 1:31 am #567551Thanks so much. This is great. Do I have to select the whole worksheet and then call the macros? Do I have to make the UsedRange like A1:c200 or does UsedRange know what is used?
Also, is there a way to move files from one folder to another within Excel? the .movefile does not seem to work. Thanks
-
WSSammyB
AskWoody LoungerFebruary 1, 2002 at 10:41 am #567596
> Do I have to select the whole worksheet
No
> does UsedRange know what is used?
Yes
> .movefile does not seem to work
Works:Option Explicit Sub Macro1() Dim fso As New FileSystemObject If fso.FileExists("c:junk.doc") Then fso.MoveFile "c:junk.doc", "c:junk" Else MsgBox "No junk" End If End Sub
Don’t forget to use Tools | References to add MS Scripting Runtime. Have a great day! –Sam
-
WSjha900
AskWoody Lounger
-
-
-
Viewing 1 reply thread -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
June 2025 updates are out
by
Susan Bradley
18 minutes ago -
Mozilla shutting Deep Fake Detector
by
Alex5723
9 hours, 7 minutes ago -
Windows-Maintenance-Tool (.bat)
by
Alex5723
9 hours, 10 minutes ago -
Windows 11 Insider Preview build 26200.5641 released to DEV
by
joep517
11 hours, 39 minutes ago -
Windows 11 Insider Preview build 26120.4250 (24H2) released to BETA
by
joep517
11 hours, 40 minutes ago -
Install Office 365 Outlook classic on new Win11 machine
by
WSrcull999
11 hours, 38 minutes ago -
win 10 to win 11 with cpu/mb replacement
by
aquatarkus
3 hours, 31 minutes ago -
re-install Windows Security
by
CWBillow
14 hours, 55 minutes ago -
WWDC 2025 Recap: All of Apple’s NEW Features in 10 Minutes!
by
Alex5723
18 hours, 36 minutes ago -
macOS Tahoe 26
by
Alex5723
12 hours, 49 minutes ago -
Migrating from win10 to win11, instructions coming?
by
astro46
54 minutes ago -
Device Eligibility for Apple 2026 Operating Systems due this Fall
by
PKCano
3 hours, 22 minutes ago -
Recommended watching : Mountainhead movie
by
Alex5723
4 hours, 6 minutes ago -
End of support for Windows 10
by
Old enough to know better
4 hours, 2 minutes ago -
What goes on inside an LLM
by
Michael Covington
28 minutes ago -
The risk of remote access
by
Susan Bradley
3 hours, 10 minutes ago -
The cruelest month for many Office users
by
Peter Deegan
10 hours, 18 minutes ago -
Tracking protection and trade-offs in Edge
by
Mary Branscombe
8 hours, 20 minutes ago -
Supreme Court grants DOGE access to confidential Social Security records
by
Alex5723
1 day, 16 hours ago -
EaseUS Partition Master free 19.6
by
Alex5723
17 hours, 37 minutes ago -
Microsoft : Edge is better than Chrome
by
Alex5723
2 days, 6 hours ago -
The EU launched DNS4EU
by
Alex5723
2 days, 18 hours ago -
Cell Phone vs. Traditional Touchtone Phone over POTS
by
280park
2 days, 9 hours ago -
Lost access to all my networked drives (shares) listed in My Computer
by
lwerman
3 days ago -
Set default size for pasted photo to word
by
Cyn
3 days, 6 hours ago -
Dedoimedo tries 24H2…
by
Cybertooth
2 days, 18 hours ago -
Windows 11 Insider Preview build 27871 released to Canary
by
joep517
4 days, 5 hours ago -
Windows 11 ad from Campaign Manager in Windows 10
by
Jim McKenna
1 day, 22 hours ago -
Small desktops
by
Susan Bradley
9 hours, 51 minutes ago -
Totally disable Bitlocker
by
CWBillow
2 days, 22 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.