Find a number, change the colour and loop

R

rjtedge

New Member
Joined
Nov 8, 2011
Messages
6
Hi,
I am trying to run a macro that will
1. open a search box
2. when a number is entered the cell is highlighted and the background colour is changed
3. Then once that cell has been changed a new search box is opened and we start again with a new number
4. the macro ends when i click cancel on the dialog box.

I have the following code, and it does the first part well, i just can get it to loop

Any idea?

<CODE>
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
</CODE>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
You could try

Code: 
Sub FindAndColour()
Dim c As Range
Dim Findstr As String
 
Findstr = InputBox("Enter search string") ' Enter your search string
If Findstr = "" Then
Exit Sub
End If
 
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
 
Call FindAndColour
 
End Sub
 
Upvote 0
Hey,

That is perfect, I also found another forum which showed a different way, which highlights just the cell, but I like how this does the whole row.



<code>
Sub find_and_colour()
Dim res
res = "enter search string here"
Do While res <> ""
res = InputBox("enter text to search for", "search", res)
If res <> "" Then
Application.ReplaceFormat.Interior.Color = 65535
Cells.Replace What:=res, Replacement:=res, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
End If
Loop
End Sub
</code></PRE></P>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Close input box
Replies
3
Views
242
annadinesh
A
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T
P
  • Question
How to find a word then cross reference to see if match
Replies
0
Views
302
Patriot2879
P
C
  • Question
Using the Find method to copy 4 cell values above Find = X to another sheet for every value of X found in a column
Replies
6
Views
368
chazrab
C
L
  • Question
VBA - Search all folders for datal/text
Replies
4
Views
112
MikeVol
MikeVol

Forum statistics

Threads
1,215,883
Messages
6,127,553
Members
449,385
Latest member
KMGLarson

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top