Get cell ref for the value which is visible

[ExcelMaker007]

Hi,

I have a value Ex: Puppy word, I will be available all over the excel, but some column and rows be hidden which may also have puppy in it...

Now I need to get the cell reference of word puppy from the visible cells only....

Please help me with vba code

 

[LazyBug]

Amend line

Set r = ActiveSheet.Range("A2:H11").SpecialCells(xlCellTypeVisible)

for your range address:

Sub EM()

Dim c As Range, r As Range, fc As String

    Set r = ActiveSheet.Range("A2:H11").SpecialCells(xlCellTypeVisible)
    
    With r
        Set c = .Find("puppy", , xlValues, , , , 0)
        If Not c Is Nothing Then
            fc = c.Address
            Do
                Set c = .FindNext(c)
                MsgBox c.Address
                If c Is Nothing Then Exit Do
        Loop While c.Address <> fc
        End If
    End With

End Sub

 

[ExcelMaker007]

Is fc is the output here?

Will we get the cell reference like Ex: B25 as output

 

[LazyBug]

Will we get the cell reference like Ex: B25 as output

What is the end goal? What do you want to do with finded cells (first cell?)?

 

[ExcelMaker007]

I need to loopup for that word to set the range to process my next step, that is coloring the column ....

so if I get the cell reference EX: B35, It will be useful to do it

 

[LazyBug]

Replace the line

MsgBox c.Address

with

c.EntireColumn.InteriorColor = vbRed