+ Reply to Thread
Results 1 to 28 of 28

Highlight cells in column of found text string

  1. #1
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Highlight cells in column of found text string

    I'm needing a macro that will select/highlight, with color, the cells within a column having a specific column heading.

    Search for "Acct" - - Highlight all the cells which contain an account number (text string) within that column which are below "Acct".

    There will only be one instance of "Acct" on the worksheet.

    Thank you,
    Delain

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    Please Login or Register  to view this content.
    If you are happy with my response please click the * in the lower left of my post.

  3. #3
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    Thanks for the quick reply, stnkynts.

    Getting an error at red line:

    Please Login or Register  to view this content.
    Runtime error 91:
    "Object variable or With block variable not set"

    What is strNum = "12345" used for?


    Thank you,
    Delain

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    I bet if I were to look at your sheet it wouldn't actually be "Acct" or it wouldnt be in row 1. Either that or you didn't input your actual account number; that is what is probably causing the error. The strNum = "12345" is for your account number. Since you didn't specify an account number I input one as an example.

    Note: You gave such limited information I had to make a lot of assumptions which is why something is going wrong.

  5. #5
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    I am wanting the macro to find the correct column to highlight the cells which are account numbers; not search for a specific account number.
    The column may not be the same each time for different worksheets.

    As I attempted to convey in my brief setup statement, yet rephrasing here:
    - Search for a column label of "Acct"
    - Highlight each cell(s) within the column under that heading which has data (account numbers).

    Sorry to occupy your time with unclear descriptions; sometimes difficult to anticipate how others will read what a person has clearly in their mind.
    Please let me know how I could have reworded my original post so that others will not shy away from offering help.

    Thank you,
    Delain

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    Submit an example workbook with example data.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Hi,

    Quote Originally Posted by delaing View Post
    ....
    Please let me know how I could have reworded my original post so that others will not shy away from offering help.
    ....
    . Nice to see someone asking that. Helps us to help you..

    ... I basically agree with Stinky Nuts.
    (.... I had highlighted this a s a Thread to attempt to answer, and was about to suggest the following when Stinky came in, so I left it as he has a lot more experience than me and can work better “blind” than i can....)

    Further I would suggest:
    ... To make it understandable to even someone like me , - inexperienced but keen to learn by answering Threads, I would suggest...

    . Give ( in addition to a good worded description as you did..) a before and after sheet. ( Or screen shots capable of being copied to a spreadsheet if you are able ( see my signature ) – NOT an Image!! ))
    . Preferably use reduced sample data, modified or made up if sensitive.

    So:
    . The before should have reduced size but well representative data.
    . The after, filled in manually by you, should look exactly as the final sheet should look like after any macro or Formula we give you is used, based on the actual sample data in the before sheet.

    . If you do that you will increase your chances of getting help as even someone like me could have a go!! Otherwise you are limited to the profi’s who amaze me how they can work “blind” sometimes – and they are mostly therefore extremely busy here!!!.

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Highlight cells in column of found text string

    @ Delain. How did you define c? I bet you had Dim c as Range. Try this modification too stnkynts' code.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    Hmmm. I had c dim as long unless he changed it. I normally put it a check for the Find but omitted it this time. Try this modification; maybe it is what you are looking for. If not then definitely submit an example workbook:

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    Alan,

    Thanks for your great suggestions and thoughts.

    Of course, the reason I didn't post a sample sheet or screenshot is I've seen code requests on this site much more complex than mine without those items and had nothing to suggest my two-step action/request would warrant the need.

    I thought >> Search for a column label of "Acct", then Highlight each cell within the column under that heading << was simple enough.
    Next time, I will aim to do better. I will certainly pause longer before posting my request.

    As an aside, How would I ". . . highlighted this as a Thread to attempt to answer..."?
    How do you highlight a thread to remind yourself to return to later?

    Thank you,
    Delain

  11. #11
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    John,

    I left everything as Stinky had it, except for what was shown in blue in my post/reply #3; thought coloring it would make my change stand out so if someone was thinking I messed up the code, they could fix my error.

    Thank you,
    Delain

  12. #12
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    Stinky,

    I did not change the Dim statement. I thought I was doing good by posting back your code with the only change that I had to make shown in bold/blue - - along with the error target shown in bold/red. I'm really disheartened that all the steps I took to try to ensure you or someone else looking in on this would see whether I had skewered your gracious offering or not.

    However . . .

    Your most recent offering in #9 is doing what I need done.
    Thank you for helping with this . . . very much.

    Is there a way that the Find is not tied to a specific Row?
    I had to change
    Please Login or Register  to view this content.
    to read
    Please Login or Register  to view this content.
    Since there are times when a row may have been added above Row-8, it would be good to be able to find the column label regardless of row.

    Delain

  13. #13
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Quote Originally Posted by delaing View Post
    ..

    Thanks....
    . Yoous welcome

    Quote Originally Posted by delaing View Post
    .... and had nothing to suggest my two-step action/request would warrant the need.

    I thought >> Search for a column label of "Acct", then Highlight each cell within the column under that heading << was simple enough.
    .....
    . It is enough for many of the very experienced people here, I was just suggesting that you reach a bigger publican the more of a good " picture" you give.....

    Quote Originally Posted by delaing View Post
    ......
    As an aside, How would I ". . . highlighted this as a Thread to attempt to answer..."?
    How do you highlight a thread to remind yourself to return to later?
    .......
    .. as you do not have too many Threads yet you can easilly find it by either looking at your Profile and then looking at your started Threads or look at your Posts. To do either of those, just click on your name up there in the left hand margin ( when you are logged in ) . Then click on
    "view posts"
    or
    "view profile " >>> " view latest started threads"
    .
    . If after a day or so you do not have a satisfactory answer, then reply to the Thread yourself and just write the word "Bump"

    . ( you could also include some more expaining detail then if you like ).

    .
    . I am altermatically notified of your posts as i habe replied in this thread. So i for one would take another look if you "Bumped" as they say...

    Alan

  14. #14
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    Is there a way that the Find is not tied to a specific Row?
    Yes, but......

    You don't want to make it too open to where the .find is having to search through too many cells. Technically we should have specified a range (and constrained it by referencing the last used column) rather than use the entire row. If you are always going to have it in either row 7 or row 8 you can specify both of them like this: Rows("7:8")

  15. #15
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    I know you can't see it right now, and there isn't a smiley offered herein that shows it, but I am jumping up 'n down with happiness.

    Love it when I get a macro to make the magic happen!
    And, hopeful that my interaction within this site will get better 'n better.

    Thank you,
    Delain

  16. #16
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    The celebrating is on hold for the moment.

    The "delaing" code from stinky (herein known as 'the stinky code') works when it remains separate as a module. But, when I insert stinky within a larger code module, that was already working properly, it gives unwanted results or no result.

    I realize I've messed this up, but I still need to learn what happened here.

    This is the original with a couple of tweaks highlighted:

    Please Login or Register  to view this content.
    Here is the larger routine with stinky inserted 'inline' to the other steps:

    Please Login or Register  to view this content.
    Here is a BEFORE version of a typical worksheet that I need to use create_MCADforPFBA with:
    FIND acct string BEFORE.xlsx

    Here is the AFTER version I am aiming for:
    FIND acct string AFTER.xlsx

    Here is the issue:
    The AFTER is a COPY of the original worksheet ("Main Page"), saved to a separate file, and the highlighting is supposed to be applied only to the COPY worksheet; NOT the original.
    The AFTER version is only attainable if I CALL the stinky code (highlight_acct) to run outside of the larger macro.

    When the stinky code is inline with the create_MCADforPFBA macro, the highlighting is applied to the original worksheet and not the copy which is created in the initial steps of the create_MCADforPFBA macro.

    The copy worksheet is saved correctly and the Shape is pasted correctly, but there is no highlighting in the copy worksheet; it only appears on the original sheet.


    Here is the BreakThoseLinks code simply for reference; there are no issues with it.
    Please Login or Register  to view this content.
    If I pull the stinky code out of create_MCADforPFBA and use a CALL, I get the AFTER version as desired.
    Please Login or Register  to view this content.

    Okay . . . that is a lot, but doing my darndest to do better so you won't have to invest so much time critiquing my posting and I can improve.

    Let me know what is not clear or what else I can submit.

    Thank you,
    Delain

  17. #17
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Quote Originally Posted by delaing View Post
    ....
    Okay . . . that is a lot, but doing my darndest to do better so you won't have to invest so much time critiquing my posting and I can improve.........
    ..

    . As I said, usually you do not need to go so far: the pros are very good at guessing what you want: -
    Stinky Nuts has been “On the Ball” from the outset, The solution I give now is not really anything other than his, All credit goes to him, I have learnt a lot from following his solutions..

    . I expect he would hit on the solution to your latest problem from just your description...

    . But your very clear last post #16 made it so clear that it both helped me to use / learn from Stinky nuts codes, and further I think I have a final working solution for you: ( In fact I think your last problem is very minor, and I actually learnt a lot from following through you code!!, (although I do not understand it all. In particular what Set StartSheet = Workbooks("start..... is doing tacked on inside The code bit containing the Stinky code is puzzelling me.... ?????)

    ...................

    . Anyways.. Your problem came as you are referring to the Main Page in the original File through the ws which is defined in the large code at the start of the code as the main FileCopy sheet in the original File, when the Main file was the Active file.. ( The reason why it worked when Call - ing it is simple: The Call will start that code when the Copy file is active, and so the ws will refer to that Currently Active File Main Page which is that of the Copy file. )

    So very simply in your last given Large code change this

    Please Login or Register  to view this content.
    To this

    Please Login or Register  to view this content.
    . The stinky code then applies to the correct sheet, which is that active, that is to say the one you see.
    . The code then works by me.
    .
    . At the end of the day you just need to be very careful and explicit in your defining of things like sheets. Otherwise problems such as your last are very common. Most profies will tell you to avoid using and relying on Active things as much as possible... I would have probably have saved the Copy file straight after you had created it through the...
    Sheets("Main Page").Select
    Sheets("Main Page").Copy
    ....bit and defined the Main Page in that File at that point,
    Or at least to minimise confusion modify your large code thus:

    Please Login or Register  to view this content.


    Alan

    P.s.
    This is an ( untypically ) good Microsoft link for the .Find Method
    https://msdn.microsoft.com/de-de/lib.../Ff839746.aspx

    and P.P.s. Here is another Smelly Code, based on what I learnt from this Thread. It has a few ways to find the “ACCT“ column ( But stinky’s second is better as it is not relying on an error handler, which is always the more professional approach

    Please Login or Register  to view this content.

  18. #18
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    The nature of your code makes it challenging to test so this is untested and I may have missed something. Either way I attempted to simplify things for you and structure in a way that is easier to follow.

    @Doc.AElstein; Good analysis. I think (not sure) that the way I have it we should still be able to reference the proper sheet. If this doesn't work then your ActiveSheet suggestion should be just fine.

    Please Login or Register  to view this content.

  19. #19
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    @ Stinky Nuts
    Hi Stinky Nuts,
    . I had been playing around with this code for fun, so nice you made another contribution...

    Quote Originally Posted by stnkynts View Post
    The nature of your code makes it challenging to test so this is untested and I may have missed something.
    ......
    . You may have missed the point that the OP was trying to produce a Copy of the File and “Do the Stinky” on that.? ( or may not ? – your point was maybe just tidying it up etc..
    Quote Originally Posted by stnkynts View Post
    .... Either way I attempted to simplify things for you and structure in a way that is easier to follow...... ......
    )

    .............................................


    . The OP creates the new file with a neat “2 liner” ...

    Please Login or Register  to view this content.
    ... which interested me, as i had not seen that before. - it is easy to miss that bit in the code. I see you did not include it and hence your code is just playing with one File...

    . Anyways I just played around with your code.. It does work, but is only as mentioned, concerned with a single File.

    . ( ..............Testing is a bit difficult because
    . a) We do not have the "start work.xlsm" . ( That would be interesting to have as i am curious what that does with those .colors stuff)
    .
    . b) Not quite sure what the Sub BreakThoseLinks does and the Call BreakThoseLinks does not work. Again maybe some more info is needed.
    ................)
    ..............................

    Anyways, For fun I combined your improvements in what I was playing about with and in the next post I will post a couple of codes . Both have some improvements from the both of us.
    . The first code is very explicit and has a quite big section to create the new Copy file giving it specific names etc. It makes virtually no use at all of Active Stuff
    . The second uses the “2 liner” and lends itself more for extensive use of Active stuff.
    .
    .
    . Maybe see what the OP has to feedback later,.........

    @ delaing
    Hi delaing,
    . Hope we are not confusing the issue too much for you. As I mentioned in Post # 17, i expect your basic problem was just that one line that you need to change...
    From

    '==================================
    With ws

    To

    '==================================
    With ActiveSheet

    .
    . But as Stinky said it is an interesting code so we have amused ourselves a bit with it. If you could supply the "start work.xlsm" it would be interesting, as would any enlightenment on what Sub BreakThoseLinks() is doing

    .....................................................................

    . Anyways in the next post ( Post #20) I include another couple of code versions.
    ( Note:
    . ( i ) for testing purposes at my end I had to comment out all references to "start work.xlsm" or Sub BreakThoseLinks() as they cause errors.....
    . (ii) I removed the On Error Resume Next, which is very bad programming practice, ( and the reason why I had to comment out erroring lines as discussed in ( i ) )

    .( iii) I changed the 'Default path name to suit me.

    . Maybe catch you later
    . Alan
    Last edited by Doc.AElstein; 08-06-2015 at 01:21 PM.

  20. #20
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Codes for post # 19



    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 08-06-2015 at 01:25 PM.

  21. #21
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    Doc,

    Ring the bell; you are the winner!!!

    . Hope we are not confusing the issue too much for you. As I mentioned in Post # 17, i expect your basic problem was just that one line that you need to change...
    From

    '==================================
    With ws

    To

    '==================================
    With ActiveSheet
    This is the simple fix to remedy my problem. Now the stinky code stinks perfectly . . . works perfectly.
    Thank you.

    It's so good to finally have found a help forum where I can mostly rely on getting a response. Most others are very hit 'n miss. You have to have developed a reputation first in order to receive viable interaction. How do you garner reputation when such a newb compared to the pros on the forum.

    To a couple of your comments . . .

    . As I said, usually you do not need to go so far: the pros are very good at guessing what you want: -
    Seems to not quite match what your very first post to me was saying:

    Further I would suggest:
    ... To make it understandable to even someone like me , - inexperienced but keen to learn by answering Threads, I would suggest...

    . Give ( in addition to a good worded description as you did..) a before and after sheet. ( Or screen shots capable of being copied to a spreadsheet if you are able ( see my signature ) – NOT an Image!! ))
    . Preferably use reduced sample data, modified or made up if sensitive.

    So:
    . The before should have reduced size but well representative data.
    . The after, filled in manually by you, should look exactly as the final sheet should look like after any macro or Formula we give you is used, based on the actual sample data in the before sheet.

    . If you do that you will increase your chances of getting help
    My original post was a result of me presuming the pros would be able to see; not guess, what I was needing.
    I guess I just need to post more requests and then I will get a better feel on how good at guessing the pros will be.

    . b) Not quite sure what the Sub BreakThoseLinks does and the Call BreakThoseLinks does not work. Again maybe some more info is needed.
    When I first implemented the first generation of the stinky code, my COPY file did not want to reopen without warning me about links to the original file. I'm not skilled enough yet with VBA to figure out why and this was not happening previously with the other VBA's I have that do almost the same thing as this new routine - sans the highlighting portion. I never received the link issue previously.
    So, I went out on the net and found a solution - BreakThoseLinks - and I let that do its thing and now no more link warning. I included the code so you could review it if needed.

    As far as sending the startwork.xlsm file - - - I can't. It includes way too much information; both proprietary and personal.
    In short,
    Please Login or Register  to view this content.
    forces the new file COPY to inherit the color scheme/palette of my longtime startwork file. I've been doing this for a long time now because I do not like the native palette of 2010.
    My worksheet colors change to very unattractive colors when the sheet is copied out and inherit(?) the native palette.

    Later in the routine,
    Please Login or Register  to view this content.
    a graphic shape with a pre-built note is pasted into the COPY file for my target user of the COPY to be reminded what I am requesting them to do with the information on COPY. The shape resides in a hidden worksheet tab within startwork.xlsm.

    Here is a screenshot of my STARTWORK.xlsm file; which is what I call my Excel "desktop". I think others refer to theirs as a launchpad or such.

    startwork shot.jpg

    Stinky, Thank you for your consideration and input. I haven't had the time to really sit with your latest offering to determine how it might improve the situation or learn from it.

    All that I do with VBA in Excel is strictly for improving my work flow; attempting to make working easier and faster. It is not an inherent requirement of the work I get paid to do.
    And, for some raisin, I have a natural hankering for Excel and using it to do things that most other 'average' users never would consider or even care about.

    So, it's a balancing act of taking time away from the work flow to push 'n poke around with VBA to create something that will speed or ease the work flow. I should know more about VBA, but run out of time and energy to devote to a regimented course or self-learning curriculum. My knowledge and code storehouse is pieced together and not the most streamlined.
    Reminds me of the old PSA commercial for drug addiction:
    I work harder so I can make more money, so I can do more coke, so I can work harder to make more money to do more coke. (As the guy is hiding out in the office toilet stall snorting another line.)

    One of the reasons you'll see such as this:
    I removed the On Error Resume Next, which is very bad programming practice,
    That came in with another snippet from some other pro whose code I copied for some purpose. Since I know error handling is needed, but don't understand error handling and how/when to implement, I left it in.


    Thank you,
    Delain

  22. #22
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Hi,
    Quote Originally Posted by delaing View Post
    ....

    This is the simple fix to remedy my problem. Now the stinky code stinks perfectly . . . works perfectly.
    Thank you. ...
    Yous welcome. And thanks for all that feedback. Unusual for new OP's and very refreshing, worth a few "Rep Points", FWIW.
    ....................................................

    Quote Originally Posted by delaing View Post
    ...
    ...... Since I know error handling is needed, but don't understand error handling and how/when to implement, I left it in.......
    .. a couple of links might help there, - it was a bit of a Pet Theme with me a few weeks back
    http://excelmatters.com/2015/03/17/on-error-wtf/
    https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo

    ... But the bottom line once you have learnt and understood it all is that you should mosty avoid it!!!!!
    ..............................................

    Quote Originally Posted by delaing View Post
    .......
    So, I went out on the net and found a solution - BreakThoseLinks - and I let that do its thing and now no more link warning. I included the code so you could review it if needed.......
    That code never worked, just errored by me. But Thanks to your explanation I see I can ignore it, as regards relevance to my testing og your program at my end. ( I do periodically get warned of some links when I have your file open, but different system, different Day etc, probably not worth me thinking too much about thet.. I just click OK to the warning and everything goes on normally. Thanks for clearing that up a bit.
    .....
    As For the .color stuff, thanks again for clearing that up. I probably would not understand how it works anyway ( i am a Beginner still!!! )

    . Alan

    P.s.,
    as for the other comments, Yeh... it is still here and everywhere a bit of Hit and Miss, - just have to accept that - But your feedback should encourage people to help,

  23. #23
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Highlight cells in column of found text string

    @delaing; Glad you got your solution and welcome to the forum. If you have a second please take a moment to mark the thread as solved.

    @Doc; Great contribution. Rep for you.

  24. #24
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Quote Originally Posted by stnkynts View Post
    ....

    @Doc; Great contribution. Rep for you.
    Thanks

  25. #25
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    I know I'll be back for more; thanks for helping.

    And thanks for all that feedback. Unusual for new OP's and very refreshing, worth a few "Rep Points", FWIW.
    Thank you,
    Delain

  26. #26
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    @ delaing

    Hi Delain,
    . I was using stuff learnt here in answering another Thread and noticed a couple things. They are vary minor, but as I know yur keen to learn I thought I would mention them un passing ( I am also keen to learn and may have got it wrong, so you can tell me if so..)

    . 1 ) I expect we both agree all the variables here should have been dimensioned as string....
    So the code line is wrong:

    Please Login or Register  to view this content.
    .. should of course be

    Please Login or Register  to view this content.
    ... otherwise these strFilename, strFoldername, strPathname default to variant, which is OK but bad practice..

    . 2 ) This line
    Please Login or Register  to view this content.
    .. should be

    Please Login or Register  to view this content.
    ..... VBA is usually kind here and gives you the cell value by default. But experienced users have told me that relying on these default implicit can come back and bite you in the Nuts when you least expect it.

    Alan

  27. #27
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Highlight cells in column of found text string

    Alan,

    I appreciate the follow up on this with the suggested refinements to the code and I've implemented them into the routine. Thanks for taking the time to do this and apology for me not taking more time and quicker to respond to this. When I first saw your post, I was a bit overwhelmed with stopping what I was involved with at the time in order to review and determine what it was about and how to apply.

    Yep, I'm keen to learn and with only limited chunks of time for the learning, so I value your input and watchfulness on this.

    Thank you,
    Delain

  28. #28
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Highlight cells in column of found text string

    Quote Originally Posted by delaing View Post
    ....
    Thank you,
    Delain
    Yous welcome, I also have limited time for my Excel learning “Hobby”, can be frustrating I know
    thanks for the reply and PM
    Alan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2014, 03:30 PM
  2. [SOLVED] IF range of cells contains string of text, return contents of cell where string is found
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-03-2013, 09:56 AM
  3. Replies: 3
    Last Post: 10-25-2012, 12:49 AM
  4. highlight a row when text found
    By Tortus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-20-2012, 07:36 AM
  5. Replies: 23
    Last Post: 09-25-2009, 07:39 PM
  6. Replies: 1
    Last Post: 09-19-2009, 08:09 PM
  7. Search column - return row found in long text string
    By JayL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1