FIND A CELL WITH A PARTICULAR PATTERN/FORMAT USING VBA!!

gab

New Member
Joined
Jul 24, 2007
Messages
24
Hi everybody,,,pls some help...

how do i find a cell with a particular pattern,,,I.E if I want to find a cell with grey shade,,,,how do i DO THAT USING VBA CODES!!!

PAnd thanks for reading this and if you have the anwer, THANKS SO MUCH!!!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
One try:
Code:
Sub Macro1()
'
Dim LR As Long  'Last Row
Dim LC As Long  'Last Column
Dim r As Range, c As Range
Dim msg As String, ans As Integer

LR = Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
LC = Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False, False).Column

Set r = Range(Cells(1, 1), Cells(LR, LC))

For Each c In r
    If c.Interior.ColorIndex = 15 Then
    msg = "Cell " & c.Address & " is light grey."
    ans = MsgBox(msg)
    End If
Next c

End Sub

This would be slow if there are many cells so I don't know if its what you want if you have thousands of cells to check. Maybe you could narrow down the search range in that case (only cells in Column A, maybe).

And...it only searches for one color, which is only one shade of grey in this case.

Regards.

(check out easyfilter at http://www.mrexcel.com/board2/viewtopic.php?t=131224 if all you need to do is filter for a cell color in a single column - I use this add-in a lot. Easyfilter requires an empty row above the data range).
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Nota bene:
Ctrl + Break will get you out if you have billions of grey cells that are being found one by one. :LOL:
 

gab

New Member
Joined
Jul 24, 2007
Messages
24
Thanks Barnes,,, But if I'm ok, the code above is to find a grey cell inside a range....Let me expand my issue:

I was working with this code:

a = Application.WorksheetFunction.Match("LATE INTEREST & BK FEES :", Range("K:K"), 0)

where "a" was the name of the row of the CELL where the string was. And that code WAS working, until I notice that the string is not always "LATE INTEREST & BK FEES :"..sometimes is another words, space, etc!!....and I'm working in a code which use a lot of files...so it's not "possible" to look in each file and take the string for the code,,,,but something I notice is that each particular CELL (when the different strings are) in all the files are under the same pattern (grey!)....

So, if you know a code similar to the initial code that I had (with the difference that instead of match for a a string, match for the grey pattern,,,, I WILL APPRECIATTE A LOT!!!!!!!!!
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Hi:
I will take it that you want a to equal the row of the cell where a grey cell interior is found in column a. What my routine does is just find the cell in Column K and returns the row of the cell use the Row property (i.e., Range("A1").Row would return 1).

I have a lot of misgivings here...using cell color seems highly risky as Excel users often have their own ways of coloring cells. If you can't control the input of the text values, you may not be able to control color either. And as mentioned earlier, this is only one shade of grey.

Nevertheless, see if this helps or gives you a start.
Code:
Sub Macro1()
'
Dim lRow As Long
Dim x As Long, a As Long
Dim c As Range
Dim booFound As Boolean

    'Find last used cell in Column K and initialize variables.
    'We don't want to use K:K because the range would take a
    'long time to search in a loop routine.
    lRow = Cells(Rows.Count, "K").End(xlUp).Row
    x = 0
    booFound = False
    '---------------------------------------------------------
    
    'Find first grey cell in the used cells of column K
    Do While booFound = False And x<= lRow
            x = x + 1
            Set c = Cells(x, "K")
            If c.Interior.ColorIndex = 15 Then
                booFound = True
            End If
    Loop
    '---------------------------------------------------------

    'Assign row to variable a if grey cell is found
    If booFound = False Then
        MsgBox "No grey cells were found"
        a = lRow
    Else
        a = c.Row
        
        'reset variables
        booFound = False
        x = 0
    End If
    '---------------------------------------------------------

    'For initial testing, this will select the cell so you can
    'see the found cell.  In the actual routine do not use this
    'statement.
    Cells(a, "K").Select

End Sub


And I tested it on this worksheet:
grey cells.xls
JKLMN
1IDTypeAmountDateUserID
22Other10007/01/07AB
34Other10007/01/07AB
45LATEINTERESTFEES:"5007/01/07AB
56INTERESTFEES:"2507/01/07AB
67Other10007/10/07AB
78Fees2507/10/07AB
89Other10007/15/07AB
Sheet1
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Nota bene:
Maybe it's not good that I assign a value of lRow to a if no grey cell is found...this was also for testing to make sure we got to the bottom row. That should be changed in your routine. The last row is surely not going to be grey every time...If you want a default numeric integer value for a (when no match is found), you could set it where I have the statement a = lRow.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub test()
Dim r As Range, ff As String, x As Range
Application.FindFormat.Interior.ColorIndex = 15
Set r = Cells.Find(What:="*", SearchFormat:=True)
If Not r Is Nothing Then
     ff = r.Address
     Do
          If x Is Nothing Then
               Set x = r
          Else
               Set x = Union(x, r)
          End If
          Set r = Cells.Find(What:="*",After:=r, SearchFormat:=True)
     Loop Unitl ff = r.Address
Else
     MsgBox "Not found"
End If
If Not x Is Nothing Then
     MsgBox "Found" & vbLf & x.Address
     x.Select
End If
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,320
You can use string pattern matching with the MATCH function.
example
Code:
a = Application.WorksheetFunction.Match("L?TE*INTEREST*B*K*FEE*", Range("K:K"), 0)

If that would work for you, it would be easier/faster than looking for colors.
 

gab

New Member
Joined
Jul 24, 2007
Messages
24
Thanks everybody

Thanks everybody!,,,the formula and the loops worked out excellent!
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

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
Top