Quick VBA code for finding cells based on .interior.pattern (or other formatting examples)

Question

Hi There!

I'm looking for help regarding a quick way to find a cell based on its interior.pattern.

I realise I can do a "For Each Cell" to loop though every cell and check with an If command, but I have hundreds of columns and I'm looking for a fast way to do it.

Ideally something similar to a .Find() command, but replacing the string of "What:=" with the interior.pattern would be perfect, but anything that's faster than looking through each cell will be much appreciated!

Thanks for the help!

P.s If someone could also tell me how to insert code snippets in the question that would be helpful!

Answer 1

H,

re:  find cell with a particular format

Finds top most cell in columns "H:L" with Red interior color.

Additional "FindNext" code required to find more cells.

SearchFormat is True in order to utilize FindFormat setting.

'---

Sub FindFirstColor()

  Dim rngFound As Range

  Dim rngToSearch As Range

  Set rngToSearch = ActiveSheet.Columns("H:L")

  Application.FindFormat.Clear

  Application.FindFormat.Interior.Color = vbRed

  Application.FindFormat.Locked = True

  Set rngFound = rngToSearch.Find(what:=vbNullString, searchformat:=True)

  rngFound.Select

End Sub

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Worked like a charm! Thanks for your help!