VBA Blogs: Find The Right Format
15 June 2018
It’s easy enough to get on a cell’s case as we explored last week, but what if we wanted to find a cell that is formatted in a particular way? Like cells that have been bolded or have a specific background colour?
This is where the SearchFormat parameter comes in. This is a Boolean parameter that tells the Find method to search a particular format. But how do we set the format?
This is where we have declare the FindFormat object. FindFormat is a special object property of the Application where one can set particular formatting details to search in the Range.Find method. All we need to do, is set the FindFormat properties as if we were setting the properties of any given Range.
For example, if we wanted to set the text to be bold, we would use:
Application.FindFormat.Font.Bold = True
Let’s review the search range we’ve been using the last few weeks:
In our range that we’ve been using, some of the cells have been highlighted yellow. The RGB code for that colour is R = 255, G = 255 and B= 0 (note this information can be found by going into the “Custom” colours and it will show the RGB code for the chosen cell). To search for that particular format, we would use:
Application.FindFormat.Interior.Color = RGB(255, 255, 0)
So how would this apply in our Macro?
Sub FindFormat()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Application.FindFormat.Interior.Color = RGB(255, 255, 0)
Dim foundrange As Range
Set foundrange = searchRange.Find("upon", Lookat:=xlPart, MatchCase:=False, searchformat:=True)
If foundrange Is Nothing Then
Debug.Print "not found!"
Else
Debug.Print foundrange
Debug.Print foundrange.Address
End If
End Sub
Easy!
Now we can add multiple formatting details to a cell. We could do this:
Sub FindFormat()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Application.FindFormat.Interior.Color = RGB(255, 255, 0)
Application.FindFormat.Font.Color = RGB(255, 0, 0)
Dim foundrange As Range
Set foundrange = searchRange.Find("upon", Lookat:=xlPart, MatchCase:=False, searchformat:=True)
If foundrange Is Nothing Then
Debug.Print "not found!"
Else
Debug.Print foundrange
Debug.Print foundrange.Address
End If
End Sub
There is a cell in that is formatted in that particular way despite there is no cell with the word “upon” in the search range. We can easily find just the format by giving it an empty string i.e. “” to search for.
Sub FindFormat()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Application.FindFormat.Interior.Color = RGB(255, 255, 0)
Application.FindFormat.Font.Color = RGB(255, 0, 0)
Dim foundrange As Range
Set foundrange = searchRange.Find("", Lookat:=xlPart, MatchCase:=False, searchformat:=True)
If foundrange Is Nothing Then
Debug.Print "not found!"
Else
Debug.Print foundrange
Debug.Print foundrange.Address
End If
End Sub
There’s a lot of different ways we could format a cell, but the one thing the FindFormat object will not allow us to set is a particular Style.
Because we can assign multiple properties in the Application.FindFormat it is easy to reset in order to search a different format later on with the simple line:
Application.FindFormat.Clear
That’s it for the Find method. Check out a new topic next week!
