Go to next line
 

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

Go to next line
 

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

Go to next line
 

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

Go to next line
 

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

Go to next line
 

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

Go to next line
 

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