finding a nbr in the mess....

[redchief]

I need help in VBA....
In cell D3 I hv a nbr, I want to search this nbr in column D6 To D9999 and highlight to me in the spreadsheet. I tried using selection.find to locate the nbr. tks v much

 

[Joe4]

Welcome to the Board!

You do not need VBA. You can do this easily with Conditional Formatting.

Here are the steps:
1. Select the range D6:D9999
2. Go to Conditional Formatting
3. Click "New Rule"
4. Click on "Use a formula to determine which cells to format"
5. Enter this formula:

=D6=D$3

6. Click on the Format button
7. Select your desired formatting option
8. Click OK twice

Here is an example of what it looks like:

If you really want/need this in VBA, you can set-up this Conditional Formatting Rule in VBA by turning on your Macro Recorder, and record yourself manually performing those steps above. That will give you the VBA code that you need to do this.

Note that one advantage to use Conditional Formatting is it is dynamic, as will updating immediately as you change data.

 

[redchief]

the nbr I Wan to find is in $D$3 which is generated by the spreadsheet. therefor I could not input the nbr into the dialog box, tks

 

[redchief]

in the selection.find....the "What:=".... is my big problem....

 

[StephenCrump]

I'd use conditional formatting myself, same as @Joe4, who has provided detailed steps in Post #2.

But if you're determined to use .Find, then try (to find just the first occurrence):

Range("D6:D999").Find(What:=Range("D3").Value).Interior.Color = vbYellow

 

[Joe4]

the nbr I Wan to find is in $D$3 which is generated by the spreadsheet. therefor I could not input the nbr into the dialog box, tks

Dialog box? What dialog box?
The method I proposed uses the cell D3. No value is hard-coded - it will always dynamically use whatever number is in cell D3.
If you change the value in D3, it will automatically and immediately update to reflect that.

 

[redchief]

ya dats it.... u hv done it....
it works.... man.....
Many tks.... Can't thank you enough...

 

[redchief]

I'd use conditional formatting myself, same as @Joe4, who has provided detailed steps in Post #2.

But if you're determined to use .Find, then try (to find just the first occurrence):

Range("D6:D999").Find(What:=Range("D3").Value).Interior.Color = vbYellow

this is the ans.... can't thank you enough.
Many thanks

 

[redchief]

I can't find the "Mark as solution" button...