Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Here are a couple of macros that hopefully do what you want:<pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    <img src=/w3timages/blackline.gif width=33% height=2>
    > i need to do this for a whole workbook
    <img src=/w3timages/blackline.gif width=33% height=2>
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    <img src=/w3timages/blackline.gif width=33% height=2>
    > Do I have to select the whole worksheet
    <img src=/w3timages/blackline.gif width=33% height=2>
    No

    <img src=/w3timages/blackline.gif width=33% height=2>
    > does UsedRange know what is used?
    <img src=/w3timages/blackline.gif width=33% height=2>
    Yes

    <img src=/w3timages/blackline.gif width=33% height=2>
    > .movefile does not seem to work
    <img src=/w3timages/blackline.gif width=33% height=2>
    Works:<pre>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</pre>

    Don't forget to use Tools | References to add MS Scripting Runtime. Have a great day! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •