Find and highlight results macro
 

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Hi,

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


HTH

"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Just to add --
Most of the code provided can be found in the vba help on the findnext
method. In both cases, since you are only marking the found cell and not
removing the searched for text, the terminating condition only needs to be:

Loop While c.Address < firstAddress

rather than

Loop While Not c Is Nothing And c.Address < firstAddress

c will never be nothing.


--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search
range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search
string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


HTH

"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for
all
occurrences of a given string and highlight the rows containing the
search
string?

What I need is a macro (or something) that will prompt me for a string
to
look for, then go through every cell and highlight the rows that contain
the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Thanks Toppers!

I tried your macro, but it didn't seem to do anything until I changed 'Loop
While' as Tom suggested.

I have a couple more question regarding this macro. Please see my next post
if you will.


"Toppers" wrote:

Hi,

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


HTH

"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Thanks Tom!

I tried Toppers macro, but it didn't seem to do anything until I changed the
'Loop While' part as you suggested.

I still have a couple of questions for you guys regarding this macro though.
Please, check out my next post.



"Tom Ogilvy" wrote:

Just to add --
Most of the code provided can be found in the vba help on the findnext
method. In both cases, since you are only marking the found cell and not
removing the searched for text, the terminating condition only needs to be:

Loop While c.Address < firstAddress

rather than

Loop While Not c Is Nothing And c.Address < firstAddress

c will never be nothing.


--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search
range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search
string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


HTH

"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for
all
occurrences of a given string and highlight the rows containing the
search
string?

What I need is a macro (or something) that will prompt me for a string
to
look for, then go through every cell and highlight the rows that contain
the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Tom,
Just FYI - in Excel 2003 both versions of the code work i.e.
with/without the "Not C is Nothing". Logically (to me!) it should work even
if it is redundant.



"Tom Ogilvy" wrote:

Just to add --
Most of the code provided can be found in the vba help on the findnext
method. In both cases, since you are only marking the found cell and not
removing the searched for text, the terminating condition only needs to be:

Loop While c.Address < firstAddress

rather than

Loop While Not c Is Nothing And c.Address < firstAddress

c will never be nothing.


--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search
range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search
string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


HTH

"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for
all
occurrences of a given string and highlight the rows containing the
search
string?

What I need is a macro (or something) that will prompt me for a string
to
look for, then go through every cell and highlight the rows that contain
the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

I didn't say it didn't work. I said it was redundant. It wasn't a
criticism of you or the author, microsoft. Just information for the OP who
is trying to learn.

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Tom,
Just FYI - in Excel 2003 both versions of the code work i.e.
with/without the "Not C is Nothing". Logically (to me!) it should work
even
if it is redundant.



"Tom Ogilvy" wrote:

Just to add --
Most of the code provided can be found in the vba help on the findnext
method. In both cases, since you are only marking the found cell and
not
removing the searched for text, the terminating condition only needs to
be:

Loop While c.Address < firstAddress

rather than

Loop While Not c Is Nothing And c.Address < firstAddress

c will never be nothing.


--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search
string

With Worksheets(1).Range("a1:D500") ' Change to reflect your
search
range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search
string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


HTH

"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search
for
all
occurrences of a given string and highlight the rows containing the
search
string?

What I need is a macro (or something) that will prompt me for a
string
to
look for, then go through every cell and highlight the rows that
contain
the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Thanks a lot topper! I think the problem with the first version not working
was probably my fault.

So, there's no way to get the currently selected fill color, eh? And, no way
to determine what the last cell (row and column) is? That's kindof bogus! :)

Anyway, thanks again for the help. I really appreciate it!

Regards,
Mick

"Toppers" wrote:

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

This seems to work to get the cell range...

Dim lastCell As String
lastCell = ActiveCell.SpecialCells(xlLastCell).Address

With Worksheets(1).Range("a1:" + lastCell)


"Toppers" wrote:

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Mick,
There are several ways of getting the last used row in a column:
this is frequently used -

Lastrow=Cells(rows.count,"A").end(xlup).row

will get last non-blank row in column A (working upwards from the bottom row
(65536).

I used "Cells" so it does the whole sheet ( as I thought this what you
wanted by saying "no hard-coded range" - wrong again!); but on limited
testing this is very fast so it isn't an overhead to search all cells.

"Mick" wrote:

This seems to work to get the cell range...

Dim lastCell As String
lastCell = ActiveCell.SpecialCells(xlLastCell).Address

With Worksheets(1).Range("a1:" + lastCell)


"Toppers" wrote:

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Now, if I can somehow get the currently selected fill color I'll have
everything I need. :)

If there's no way to do that, then I guess I'll have to make my own custom
'Find & Highlight' dialog to get the search parameters and highlight color.

Thanks!

"Toppers" wrote:

Mick,
There are several ways of getting the last used row in a column:
this is frequently used -

Lastrow=Cells(rows.count,"A").end(xlup).row

will get last non-blank row in column A (working upwards from the bottom row
(65536).

I used "Cells" so it does the whole sheet ( as I thought this what you
wanted by saying "no hard-coded range" - wrong again!); but on limited
testing this is very fast so it isn't an overhead to search all cells.

"Mick" wrote:

This seems to work to get the cell range...

Dim lastCell As String
lastCell = ActiveCell.SpecialCells(xlLastCell).Address

With Worksheets(1).Range("a1:" + lastCell)


"Toppers" wrote:

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

Mick,
You say " currently selected fill color " - by this, do you
mean you a cell actual selected? If so, then:

ActiveCell.Interior.ColorIndex will give you the colour.

If you don't have a cell selected, then I can't see how you can get the
colour as your reply also suggests there is (can be ) more than one colour.

One possible way is to have a palette of cells with your colours and select
one prior to invoking your macro. Or simply store the "currently selected
colour" - however this is determined - and then recall in your macro.

I've run out of ideas!

Hope you find a solution.

"Mick" wrote:

Now, if I can somehow get the currently selected fill color I'll have
everything I need. :)

If there's no way to do that, then I guess I'll have to make my own custom
'Find & Highlight' dialog to get the search parameters and highlight color.

Thanks!

"Toppers" wrote:

Mick,
There are several ways of getting the last used row in a column:
this is frequently used -

Lastrow=Cells(rows.count,"A").end(xlup).row

will get last non-blank row in column A (working upwards from the bottom row
(65536).

I used "Cells" so it does the whole sheet ( as I thought this what you
wanted by saying "no hard-coded range" - wrong again!); but on limited
testing this is very fast so it isn't an overhead to search all cells.

"Mick" wrote:

This seems to work to get the cell range...

Dim lastCell As String
lastCell = ActiveCell.SpecialCells(xlLastCell).Address

With Worksheets(1).Range("a1:" + lastCell)


"Toppers" wrote:

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?

Find and highlight results macro
 

I want to use the fill color that is currently selected in the formatting
toolbar of the Excel main widow. I thought there might be some way to get
this color--maybe using the Application object?

Actually, I'd prefer is to display a color palette in the same dialog that
prompts the user for the text to search for. Or, if that's not possible, to
display a color selector dialog right after the user specifies the text to
search for.

The idea is to have a macro that will let the user highlight rows with one
color that contain a specified text string, then run the macro again and
highlight other rows with a different color.


"Toppers" wrote:

Mick,
You say " currently selected fill color " - by this, do you
mean you a cell actual selected? If so, then:

ActiveCell.Interior.ColorIndex will give you the colour.

If you don't have a cell selected, then I can't see how you can get the
colour as your reply also suggests there is (can be ) more than one colour.

One possible way is to have a palette of cells with your colours and select
one prior to invoking your macro. Or simply store the "currently selected
colour" - however this is determined - and then recall in your macro.

I've run out of ideas!

Hope you find a solution.

"Mick" wrote:

Now, if I can somehow get the currently selected fill color I'll have
everything I need. :)

If there's no way to do that, then I guess I'll have to make my own custom
'Find & Highlight' dialog to get the search parameters and highlight color.

Thanks!

"Toppers" wrote:

Mick,
There are several ways of getting the last used row in a column:
this is frequently used -

Lastrow=Cells(rows.count,"A").end(xlup).row

will get last non-blank row in column A (working upwards from the bottom row
(65536).

I used "Cells" so it does the whole sheet ( as I thought this what you
wanted by saying "no hard-coded range" - wrong again!); but on limited
testing this is very fast so it isn't an overhead to search all cells.

"Mick" wrote:

This seems to work to get the cell range...

Dim lastCell As String
lastCell = ActiveCell.SpecialCells(xlLastCell).Address

With Worksheets(1).Range("a1:" + lastCell)


"Toppers" wrote:

Better still ..

Dim MyRange As Range
Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) '
DataType is Range
Colour = MyRange.Interior.ColorIndex

"Toppers" wrote:

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole worksheet using Cells.

How do we know what is the currently selected fill colour?

If you already have an coloured cell selected PRIOR to running the macro
(this is the Activecell), then you could use:

c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex

Equally, you could enter the address of a coloured cell and use:

ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1
Colour = Range(ColAddr).Interior.ColorIndex


HTH

P.S. Sorry about the earlier code problem but both versions work in Excel 2003

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


Sub FindAndColour()

Dim c As Range
Dim Findstr As String
Dim Colour As Integer

Findstr = InputBox("Enter search string") ' Enter your search string
Colour = InputBox("Enter Colour code") ' Enter value for colour code

With Worksheets(1).Cells ' Whole sheet
Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = Colour ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While c.Address < firstAddress
End If
End With
End Sub


"Mick" wrote:


The code you guys gave me does everything I want... almost. :)

You guys are great, and I really appreciate your help, but I have a couple
more questions...

1. Would it be possible to...
a. Have it use the currently selected fill color for the highlighting?
b. Prompt for a color to use for the highlighting?

2. Can I make it search the entire worksheet, without a hard-coded Range?

Sub FindAndColour()

Dim c as range
Dim Findstr As String

Findstr = InputBox("Enter search string") ' Enter your search string

With Worksheets(1).Range("a1:D500") ' Change to reflect your search range
Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = 4 ' Set row to green
Set c = .FindNext(c) ' Look for next occurence of search string
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"Mick" wrote:

Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search
string?

What I need is a macro (or something) that will prompt me for a string to
look for, then go through every cell and highlight the rows that contain the
string I specified.

Does anyone have a macro for this?

If not, can someone please help me create a macro that will do this?