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 i
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?

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=51310

Cycle through each row and look for string in each cell
 

Hi pal,

This is the code to search through all your values in your cells;

Code:
--------------------

i = 1:
Do
If InStr(1, Cells(i, 1).Value, "Total", vbTextCompare) < 0 Then
'change cell colour code here
End If
i = i + 1
Loop Until Cells(i, 1).Value = ""

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=513103

Cycle through each row and look for string in each cell
 

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) <
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'v
never come across, and the first '1' in the InStr...

thanks very much,

Mat

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=51310

Cycle through each row and look for string in each cell
 

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.


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=513103

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" wrote in
message ...

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

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