How to Find and Select a cell using VBA / Macro

Question

How to Find and Select a cell using VBA / Macro

Paul Rimmer 0

I want to find a cell that holds a given numeric parameter. Having found that cell, how do I select it so that I can format the cell?

This is part of the code that I recorded as a macro:

Cells.Find(What:="73", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _

    *xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _*

    *, SearchFormat:=False).Activate*

***Range("G3").Select***

*With Selection.Interior*

    *.Pattern = xlSolid*

    *.PatternColorIndex = xlAutomatic*

    *.Color = 65535*

    *.TintAndShade = 0*

    *.PatternTintAndShade = 0*

*End With*

My question relates to the Range.Select in the macro. I selected cell G3 by clicking on it as the macro recorded. What code do I use if I want the macro to identify cell G3 on its own?

Thank you

0 comments

2 answers

Your answer

Answer 1

Barry Schwarz 5,591

Since the Find activates a range. you can replace your range-select statement with

Activecell.Select

This will allow the rest of your code to operate on the cell found.

0 comments

Answer 2

HansV 462.6K MVP Volunteer Moderator

There is no need to select the cell. You can use code like this:

    Dim rngFound As Range
    Set rngFound = Cells.Find(What:="73", After:=ActiveCell, _
        LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rngFound Is Nothing Then
        MsgBox "Value not found!", vbExclamation
    Else
        rngFound.Interior.Color = vbYellow
    End If

0 comments

Share via

How to Find and Select a cell using VBA / Macro

2 answers

Your answer