Conditional Formatting Challenge

[Exceller]

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.

 

[JLGWhiz]

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

 

[Rick Rothstein]

Two comments on your code...

First, I don't think the 'c' variable can ever be Nothing, so you should be
able to remove that test. Second, I think using an InStr test instead of the
Like comparison might be better for that particular search (case insensitive
word). Maybe this...

Sub MIT()
Dim C As Range
For Each C In Sheets(1).UsedRange
If InStr(1, C.Value, "apple", vbTextCompare) Then
C.Interior.ColorIndex = 3
End If
Next
End Sub

 

[JLGWhiz]

Hi Rick, I put the Nothing criteria in out of habit to avoid messages in
case it does not find anything. But you are right, it is superflous in this
case. The whole point was to illustrate that 'apple" can be found no matter
how many spaces it has or what other data may be in the cell. I don't
believe either of our snippets will answer the question of conditional
formatting to do the same job, however.