I need to add some formatting in a spreadsheet in the row after a specific
value is found

The word "New" is my target word so

What I need to do is is go to the line after each time the word New is found

Any suggestions

Your question is a little vague but perhaps this will get you started...

Public Sub FindNew()
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="New", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "New was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(1, 0).EntireRow.Interior.ColorIndex = 34
End If
End Sub
--
HTH...

Jim Thomlinson


"Nigel" wrote:

I need to add some formatting in a spreadsheet in the row after a specific
value is found

The word "New" is my target word so

What I need to do is is go to the line after each time the word New is found

Any suggestions

Once I have found it I need to move to the next row apply some formatting
and then move on the the next instance of new


"Jim Thomlinson" wrote:

Your question is a little vague but perhaps this will get you started...

Public Sub FindNew()
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="New", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "New was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(1, 0).EntireRow.Interior.ColorIndex = 34
End If
End Sub
--
HTH...

Jim Thomlinson


"Nigel" wrote:

I need to add some formatting in a spreadsheet in the row after a specific
value is found

The word "New" is my target word so

What I need to do is is go to the line after each time the word New is found

Any suggestions

OK I am sorry I do need to be more specific

In column A the value will be either new or old ie a2 value is old a3 value
is old a4 value is new - a5 is blank a6 is old and so on


after the value new there will aways be a blank line and then after that a
new line of data

What I need to do is go to the first blank line and add some calculations
then move on and find the next intance of new - move to next line do
calculations - continue on to next blank line until the last row of data is
reached

"Jim Thomlinson" wrote:

Your question is a little vague but perhaps this will get you started...

Public Sub FindNew()
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="New", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "New was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(1, 0).EntireRow.Interior.ColorIndex = 34
End If
End Sub
--
HTH...

Jim Thomlinson


"Nigel" wrote:

I need to add some formatting in a spreadsheet in the row after a specific
value is found

The word "New" is my target word so

What I need to do is is go to the line after each time the word New is found

Any suggestions

Take a look at this...

Public Sub FindNew()
Dim rngFound As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="New", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "New was not found."
Else
strFirstAddress = rngFound.Address
Do
rngFound.Offest(1,0).Select
msgbox "Tada... time to do my stuff"
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub

--
HTH...

Jim Thomlinson


"Nigel" wrote:

OK I am sorry I do need to be more specific

In column A the value will be either new or old ie a2 value is old a3 value
is old a4 value is new - a5 is blank a6 is old and so on


after the value new there will aways be a blank line and then after that a
new line of data

What I need to do is go to the first blank line and add some calculations
then move on and find the next intance of new - move to next line do
calculations - continue on to next blank line until the last row of data is
reached

"Jim Thomlinson" wrote:

Your question is a little vague but perhaps this will get you started...

Public Sub FindNew()
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="New", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "New was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(1, 0).EntireRow.Interior.ColorIndex = 34
End If
End Sub
--
HTH...

Jim Thomlinson


"Nigel" wrote:

I need to add some formatting in a spreadsheet in the row after a specific
value is found

The word "New" is my target word so

What I need to do is is go to the line after each time the word New is found

Any suggestions

Look in the vba help index for FIND and FINDNEXT.

--
Don Guillett
SalesAid Software

"Nigel" wrote in message
...
I need to add some formatting in a spreadsheet in the row after a specific
value is found

The word "New" is my target word so

What I need to do is is go to the line after each time the word New is
found

Any suggestions