I am not sure what you are looking for, but this snippet will find apple,
regardless of the spaces or capitalization or what comes before or after it.

Sub mit()
For Each c In Sheets(1).UsedRange 'Change to suit
If Not c Is Nothing Then
If UCase(c.Value) Like "*APPLE*" Then
c.Interior.ColorIndex = 3
End If
End If
Next
End Sub


"Exceller" wrote:

I've searched both posts here and on the internet, but have not found a
solution to my issue. I work in general ledgers all day and need to be able
to format the sheet whereby any instance of a particular account gets
highlighted, wherever it is on the worksheet. The problem with my particular
circumstance is that there can be various permutations of the Account Name,
depending on the number of spaces in front of it. The number of spaces that
are placed before the account name is highly variable and is determined by
the amount of "drill down" I do in the account hierarcy.

For the sake of simplicity, say I have accounts "Apple", "Orange" and
"Pear". Depending on how far down I drill on the Apple account (or other
accounts) it can appear as ' Apple, or ' Apple, or any variant of
"Apple" and preceding spaces. Note that there is an apostraphe, then any
number of spaces and then the account name. I've tried various formulae in
the Conditional Formatting utility in Excel and have not been able to "get
there", including Clean(Trim(xxxxxx and Find( and Search( functions. I'd
rather have this in VBA anyway.

So, in closing, If "Apple" is found anywhere on the worksheet, regardless of
how many spaces exist between the beginning apostrophe and the account name I
want it --and the next 10 cells to the right highlight yellow and have a
border box around the range.

Is this do-able?

Thanks.