RE: Find and highlight results macro

Tech-Archive recommends: Fix windows errors by optimizing your registry

Mick,
I have just added another input box to ask for the colour code
(number) and extended the range to whole work*** 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 work***, 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?
> >
> >
.

Quantcast