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.
"Rick Rothstein" wrote:
> 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
>
> --
> Rick (MVP - Excel)
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:580BA370-F222-4B91-81B4-(E-Mail Removed)...
> >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.
> >>
> >>
>
>
|
Similar Threads
