search/replace numbers and make red (excel xp, w2000)

Topic

New Reply

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!

Reply | Quote

Replies

A couple of quick questions:

1- Would it be OK to do this with conditional formatting, or do you actually want the cell format changed?

2- Do you want to do this for all cells on all sheets in a workbook, or just on some specific range of cells on one sheet?

Reply | Quote

yes, i think i could use a conditional formatt in the cell. Yes, i need to do this for a whole workbook for every worksheet in it. thanks a lot for the help

Reply | Quote

> 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

Reply | Quote

OK, 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.

Reply | Quote

Here 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

Reply | Quote

Thanks 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

Reply | Quote

> 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

Reply | Quote

ok, yep that worked. I forgot the “new” in the dim stmt. thanks for the help. i’m on my merry way now!

Reply | Quote