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

[gab]

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!!!

 

[xenou]

One try:

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]

Nota bene:
Ctrl + Break will get you out if you have billions of grey cells that are being found one by one.

 

[gab]

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]

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.

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
	J	K	L	M	N
1	ID	Type	Amount	Date	UserID
2	2	Other	100	07/01/07	AB
3	4	Other	100	07/01/07	AB
4	5	LATEINTERESTFEES:"	50	07/01/07	AB
5	6	INTERESTFEES:"	25	07/01/07	AB
6	7	Other	100	07/10/07	AB
7	8	Fees	25	07/10/07	AB
8	9	Other	100	07/15/07	AB
Sheet1

 

[xenou]

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]

Hi
try

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]

You can use string pattern matching with the MATCH function.
example

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]

Thanks everybody

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