+ Reply to Thread
Results 1 to 6 of 6

Cycle through each row and look for string in each cell

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    164

    Cycle through each row and look for string in each cell

    Hi there,

    I need to cycle through every row of data, look in column A and find if the string "Total" exists in the cell.
    the string will be part of another string. e.g. "D01234-A Total".

    Every instance that is found of a cell in column A containing Total, eill require the entire row to be made 'Bold' font.

    does this sound do-able??

  2. #2
    Forum Contributor
    Join Date
    01-19-2006
    Posts
    142
    Hi pal,

    This is the code to search through all your values in your cells;
    Please Login or Register  to view this content.
    Added the cell change colour too! hope it works!
    Last edited by gti_jobert; 02-16-2006 at 05:30 AM.

  3. #3
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    164
    hi,

    thanks for the code!
    it works like a charm!

    I amended it to bold the entire row:

    (not sure how to make a code winod in this forum)

    i = 1:
    Do
    If InStr(1, Cells(i, 1).Value, "Total", vbTextCompare) <> 0 Then
    Cells(i, 1).EntireRow.Font.Bold = True
    End If
    i = i + 1
    Loop Until Cells(i, 1).Value = ""



    out of interest, can you explain the parameters in the InStr function.
    I understand what the Cells(i,1).Value does, but the vbTextCompare i've never come across, and the first '1' in the InStr...

    thanks very much,

    Matt

  4. #4
    Forum Contributor
    Join Date
    01-19-2006
    Posts
    142
    Hello again, glad to hear it works fine - I only found out how to do it myself yesterday when i was building a search tool using the InStr function.

    http://www.techonthenet.com/excel/formulas/

    This is a good site to have a look at, it explaing how alot of functions work inc the InStr one.

  5. #5
    Bob Phillips
    Guest

    Re: Cycle through each row and look for string in each cell

    The 1 tells it to start looking at the first character.
    vbTextCompare tells it to make a text comparison, which is not case
    sensitive.

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "matpj" <[email protected]> wrote in
    message news:[email protected]...
    >
    > hi,
    >
    > thanks for the code!
    > it works like a charm!
    >
    > I amended it to bold the entire row:
    >
    > (not sure how to make a code winod in this forum)
    >
    > i = 1:
    > Do
    > If InStr(1, Cells(i, 1).Value, "Total", vbTextCompare) <> 0
    > Then
    > Cells(i, 1).EntireRow.Font.Bold = True
    > End If
    > i = i + 1
    > Loop Until Cells(i, 1).Value = ""
    >
    >
    >
    > out of interest, can you explain the parameters in the InStr function.
    > I understand what the Cells(i,1).Value does, but the vbTextCompare i've
    > never come across, and the first '1' in the InStr...
    >
    > thanks very much,
    >
    > Matt
    >
    >
    > --
    > matpj
    > ------------------------------------------------------------------------
    > matpj's Profile:

    http://www.excelforum.com/member.php...o&userid=21076
    > View this thread: http://www.excelforum.com/showthread...hreadid=513103
    >




  6. #6

    Re: Cycle through each row and look for string in each cell

    Hello,

    The easiest / fastest solution i would suggest is to use conditional
    formatting.
    Select the first row of data in your sheet and use the conditional
    format of...

    [Formula is ] =NOT(ISERROR(FIND("total",A2)))

    Where 'total' is the string you want to find and A2 is the cell to
    check your string for.

    Simply use the format painter to paint the conditional formats for the
    rest of the rows in your sheet and viola!!!

    note: when you paint the formats the cell reference will change to
    compliment where you paint the conditional format.

    You shouldn't need VB, unless am missing the point?

    -davemac


    matpj wrote:
    > Hi there,
    >
    > I need to cycle through every row of data, look in column A and find if
    > the string "Total" exists in the cell.
    > the string will be part of another string. e.g. "D01234-A Total".
    >
    > Every instance that is found of a cell in column A containing Total,
    > eill require the entire row to be made 'Bold' font.
    >
    > does this sound do-able??
    >
    >
    > --
    > matpj
    > ------------------------------------------------------------------------
    > matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
    > View this thread: http://www.excelforum.com/showthread...hreadid=513103



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1