FindFormat Help.

Baziwan

New Member
Joined
Sep 4, 2018
Messages
32
Hi all,

I have a table that has multiple matching entries and i'm trying to use .find to get the right match.
Here's where it get's complicated. I want it to find the first match with a specific font format.
eg.
Row 1 apples in font yellow 0
Row 2 Bananas in font yellow 0
Row 3 Apples in default font 25
Row 4 Bananas in default font 50
the rows are conditionally formatted so in row 2 if the value in column 2 is <=0 then bananas font is in yellow. if it's >0 then the format is default.
So I'm trying to get my .find to find the first match with default font.
I've played around with the find in the macro recorder and it can be done but when i try to apply it into my
sub the .find is nothing
Code:
Application.FindFormat.NumberFormat = "General"
Application.FindFormat.Locked = True          
Set rCl = .Find(sFind, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=True)
Here's the code that I attempted but the rCl is returning as nothing. When i remove the findformat lines & the SearchFormat the rCl returns a match as I expect.
I know that it can be done but I just don't know what I'm doing wrong.
Can anyone help??
Thanks in advance
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
The macro below works

Test like this...
- add a new worksheet
- in A1, A2 and A3 enter the word happy (in all 3 cells)
- UNLOCK cell A1
- change numberformat to TEXT for A2
- run the macro below - it returns A3 (the only cell that matches all conditions)

Code:
Sub FindFirst()
    With Application.FindFormat
        .NumberFormat = "General"
        .Locked = True
    End With     
    MsgBox ActiveSheet.Cells.Find(what:="happy", searchformat:=True).Address(0, 0)
End Sub

Now change things ONE at a time until you establish what is causing the search to return nothing for you
 
Last edited:
Upvote 0
Hi Yongle,

Thanks for the reply. This has helped. I've discovered what may be the issue. Although my conditional formats are changing the cell colour, when I go to format the cell it still shows the cell format as automatic even though they've been conditionally formatted to a different colour. Any idea how I can get around this?

regards
 
Upvote 0
That is your problem
Conditional format is a mirage
Easiest way is to test for the conditional format condition - ie if cell is red because cell value is greater than 200, then test for cell value greater than 200
 
Upvote 0
Hi,

Yeah that is a real bummer. Unfortunately the sfind is the product name & that's what's
conditionally formatted. I was hoping to speed up my code by having the sfind go straight
to the first match that met the format.
I've been using .findnext with an if condition. eg if rCl.offset(Rw, 2).value <=("0") then findnext
but that involves a lot of looping that slows the process down as there are some products that have
over 100 rows that match but have no value.
Is there a way to have a condition in the .find that it goes strait to the first match where the offset is <=0

regards
 
Upvote 0
I will post amended code when back at my PC in a few hours.
 
Upvote 0
How about something like
Code:
Sub test2()
   Dim Fnd As Range
   Dim i As Long, Qty As Long
   
   Qty = Application.CountIf(Range("I:I"), "St Albans")
   Set Fnd = Range("I1")
   For i = 1 To Qty
      Set Fnd = Range("I:I").find("St Albans", Fnd, , xlWhole, , , False, , False)
      If Fnd.DisplayFormat.Font.Color = vbYellow Then
         Fnd.Select
         Exit For
      End If
   Next i
End Sub
 
Upvote 0
How about something like
Code:
Sub test2()
   Dim Fnd As Range
   Dim i As Long, Qty As Long
   
   Qty = Application.CountIf(Range("I:I"), "St Albans")
   Set Fnd = Range("I1")
   For i = 1 To Qty
      Set Fnd = Range("I:I").find("St Albans", Fnd, , xlWhole, , , False, , False)
      If Fnd.DisplayFormat.Font.Color = vbYellow Then
         Fnd.Select
         Exit For
      End If
   Next i
End Sub
If a yellow-font St. Albans is in cell I1 and if there are more than one yellow-font St. Albans in the column, your code will ignore the one in cell I1 and find the second one in the column. To correct that, you need to specify the After argument and set it to the last cell in the column.
 
Upvote 0
Mirror conditional formatting in VBA
Loop through an array of values and
- use Range.Find and Range.FindNext to find a match for each value and test against other criteria until the first complete match is found
- test within the loop to identify the first from all the matches found

A simple non-real example
NB - there is a simpler way to find this particular match but this serves to illustrate my method
The VBA below
- looks for the first match of any of 3 specific customers with sales > 99 and Type = "AB"
- and correctly returns address B9 (cust2)

The conditional formatting formula in column B is
=AND(C1>99,D1="AB",OR(B1="cust1",B1="cust2",B1="cust3"))

[TABLE="width: 284"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Customer[/TD]
[TD]Sales[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD="align: right"]17/10/2018[/TD]
[TD] cust30[/TD]
[TD="align: right"]100[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]17/10/2018[/TD]
[TD] cust31[/TD]
[TD="align: right"]109[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust32[/TD]
[TD="align: right"]144[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust15[/TD]
[TD="align: right"]118[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust1[/TD]
[TD="align: right"]120[/TD]
[TD] CD[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust2[/TD]
[TD="align: right"]101[/TD]
[TD] CD[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust3[/TD]
[TD="align: right"]146[/TD]
[TD] CD[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust2[/TD]
[TD="align: right"]120
[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]18/10/2018[/TD]
[TD] cust3[/TD]
[TD="align: right"]91[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]19/10/2018[/TD]
[TD] cust4[/TD]
[TD="align: right"]61[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]19/10/2018[/TD]
[TD] cust1[/TD]
[TD="align: right"]105[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]19/10/2018[/TD]
[TD] cust6[/TD]
[TD="align: right"]108[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]19/10/2018[/TD]
[TD] cust7[/TD]
[TD="align: right"]135[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]19/10/2018[/TD]
[TD] cust8[/TD]
[TD="align: right"]100[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]20/10/2018[/TD]
[TD] cust3[/TD]
[TD="align: right"]100[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]21/10/2018[/TD]
[TD] cust4[/TD]
[TD="align: right"]94[/TD]
[TD] AB[/TD]
[/TR]
[TR]
[TD="align: right"]21/10/2018[/TD]
[TD] cust3[/TD]
[TD="align: right"]125
[/TD]
[TD] AB[/TD]
[/TR]
</tbody>[/TABLE]

Code:
Option Explicit
Sub FindCond()

Dim first As Range, cel As Range, rng As Range, addr As String, cust As Variant, msg As String
Set rng = ActiveSheet.Range("B2:B100")
Set first = ActiveSheet.Cells(Rows.Count, 1)
On Error Resume Next
With rng
    For Each cust In Array("cust1", "cust2", "cust3")
        Set cel = .Find(cust, lookat:=xlWhole)
        If Not cel Is Nothing Then
            addr = cel.Address
            If PassTest(cel, first) Then
                Set first = cel
            Else
                Do
                    Set cel = .FindNext(cel)
                    If PassTest(cel, first) And cel.Address <> addr Then
                         Set first = cel: Set cel = Nothing: addr = cel.Address
                    End If
                Loop While Not cel Is Nothing And cel.Address <> addr
            End If
        End If
        Set cel = Nothing: addr = ""
    Next cust
End With

If first.Row < Rows.Count Then msg = first.Address(0, 0) Else msg = "no matches"
MsgBox msg

End Sub
Code:
Private Function PassTest(cel As Range, first As Range) As Boolean
    If cel.Offset(, 1) > 99 And cel.Offset(, 2) = "AB" And cel.Row < first.Row Then
        PassTest = True
    Else
        PassTest = False
    End If
End Function
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

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
Back
Top