Macro Inquiry - Beginner Support

  • I've been reading many tutorials but have not had any luck trying to figure out what seems to be a simple macro I'm attempting to write, hence the reason I'm coming to the experts here. What I am attempting to do is simply search an excel sheet for a specific string of text and once it find this text it will highlight the entire column the text is located in in a different color. Seems pretty easy on paper, but I am just wrestling with this thing and getting nowhere. Any help would be appreciated.

  • Re: Macro Inquiry - Beginner Support


    I didn't mention this in the first post, but I would need for it to loop until the end of the excel sheet. There will be multiple selections of the string of text I'm searching for and highlighting.

  • Re: Macro Inquiry - Beginner Support


    Hi and welcome to OzGrid.


    To the end of the worksheet would take a lot longer. The code can find the used range on the worksheet and only search that area -- quicker depending on just how much of the worksheet is used.


    Just to clarify, the search string could be found within the contents of a cell, in other words, the whole cell wouldn't match the search string.


    Even though there may be multiple occurrences of the search string, as soon as the search string is found in a column (1st occurrence in column), then the whole column is highlighted (filled with a colour). Then the search can move onto the next column and search without finishing the column?


    Also, you realize that highlighting, which is fillcolor, often removes the gridlines. Can you turn on the macro recorder and select a column and fill with the colour you want and any borders you want to add and then turn off the recorder. Then post the cost here.


    When posting code remember to use the code tags, either [ code][i]your code[i][ /code] or [ vba][i]your code[i][ /vba] without the space after [.


    That way your preferences can be incorporated into any suggestion.


    Last, but probably not least, is how do you want to trigger the code. A button or command button on the worksheet? From the menu using Tools>Macro>Macros... and select the macro to run?

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    Oops! Told you not the last. This is for just one worksheet in the workbook? or do you want it to check each worksheet in the workbook?


    If just one, do you have the worksheet name or will it always be the activesheet?


    If all worksheets, any to be excluded?

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    The text string I will be searching for will be located in a specific column and only in that one column. I would like for it to basically scan through that entire column and each cell it comes to that equals a specific string of text it will highlight the entire row it is located in in a specific color. I listed below the code that I am looking for the row to be highlighted in. Also, I would like the macro to be set to a specific hot key that I could easily trigger to begin the search.


    Code
    With Selection.Interior
            .ColorIndex = 4
            .Pattern = xlSolid
        End With
  • Re: Macro Inquiry - Beginner Support


    This will be for one worksheet only. No need to cycle through to a different worksheets in the workbook. It will always be the active sheet.

  • Re: Macro Inquiry - Beginner Support


    Okay, some good feedback (the code) but some conflicting information from what you first posted. So now you want the row, not the column highlighted unlike your first post where you indicated the column?


    Will you only be searching 1 column then? or is it that the text will only be found in 1 column on the worksheet and you don't know which column? So if it has to search for which column, by what you said, I assume that it need only restrict the search to the column where the first occurrence was found in order to find and highlight additional occurrences.


    So when you say

    Quote

    There will be multiple selections of the string of text I'm searching for and highlighting.

    you mean that this time you search for this search and the next time you search for another? or are you saying that you want to search for several different search strings at the same time?


    Each time you run the code, will you want to input the search string?


    As often happens here, you think you want this and then it's that and then maybe the other one. So it's important to get your requirements straight up front if possible. :)

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    Hmm, I seem to be complicating the issue the more I post. Doh! Let me try to explain myself again.


    I will break it down to a simple form of what I'm looking for. Let's say I have 2 Columns setup. The first column with a bunch of random names, the 2nd column with either a string of text stating either "Yes" or "No". I would like for the macro to search the entire 2nd column, each time it comes to a cell in that column where the string="Yes" then I would like for it to highlight the row for both columns 1 and 2. After it highlights that row it will continue down the column until it finds the next string of text ="Yes", then highlight that row, etc.

  • Re: Macro Inquiry - Beginner Support


    Could you use Conditional Formating or do you want a macro? CF would be very easy in this case, but if you are trying to learn VBA, then a macro solution works toward that end?

  • Re: Macro Inquiry - Beginner Support


    At this point it doesn't make much difference, as long as it works I'd be happy. Guess it would be nice to see both ways for future reference, but anyway would be fine. :)

  • Re: Macro Inquiry - Beginner Support


    Not to worry but what I see happening more often than not is a person says this and then they get the code and they actually wanted that.


    And as you hang out here, you'll find that there is often more than 1 way to do something: first there is the Excel way and then there is the VBA way and within each of those there can be more than 1 way.


    The end result is what is important. Whatever you can do and understand (read that you could actually take what you were given and apply it else where) that gets the job done. And then as you progress as many of us have, you realize that there were more flexible and powerful ways to do things.


    I might take the time to point out something here. If you have a large file and then take several rows, select the ENTIRE row and format them with a fille (or any other formatting), the formatting is applied from A:IV. This can greatly increase the size of your file. So, do you really want the entire row highlighted or just those columns in your used range, for example A:B?


    Attaching your file, or a small sample of your file, often helps us help you better. If as you say in your previous post that names are in Column A and either "Yes" or "No" in Column B, then you have a few options.


    As Seti said, if that is the way your data is laid out, then Conditional Formatting using Excel (and copied down to all rows) is your simplest method provided you don't have more than 3 conditions. However, depending on how you want to format you may be able to stretch that to 6 conditions but generally it's understood that with more than 3 conditions you need to go to VBA for your solution.


    Your method of running the code requires that you make a decision at some point to run the code, find the "Yes"'s and highlight.


    If you had specified that everytime I enter Yes in a cell in Column B, then have the code automatically run and highlight either the entire row or just the cells for that row in the used range. If I enter No, then either remove the highlighting or use a different colour.


    Obviously the latter would be the most flexible and powerful, if it fits your application but few of us even know about that capability. Someimes I think have a this is the minimum I want/need and if I had my wish and it could be done, this is what I want/need. :)

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    Quote from Maze

    At this point it doesn't make much difference, as long as it works I'd be happy. Guess it would be nice to see both ways for future reference, but anyway would be fine. :)

    Attach your file. If it has confidential information, change the names to protect the innocent (and the not so innocent). If it's too large, remove rows but leave all the columns but make sure the rows give a good idea of what your data would look like. If still too large, try zipping. If still too large try removing extraneous worksheets, etc. Or if you want to send the file intact, just PM me and I'll send you my email address.


    Then I'll give you an example of all methods discussed. You can use it to learn. The quicker you can get to the powerful stuff, the better your life will be (or at least mine was). I just wish I had found this forum long ago but we can all appreciate that sometimes you need to crawl, then walk, then run -- then you can enter the marathon. :)

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    Here's the Conditional Formatting method. No programming required and no button but you either need to apply the condition formatting from row 1 to 65536 or keep applying it as your list grows.

  • Re: Macro Inquiry - Beginner Support


    Okay but usually don't recommend posting your email address because then it becomes public record. You can edit this message to remove it if you want. I have it now.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    Quote from Maze

    Yea no worries, it is just a junk email for situations such as these. I did edit it out though. :)

    I see you have your profile set to receive emails so that's okay. Not quite as public because I don't think the webcrawler can ferret that out.


    And yes, you can get enough junk emails without a published email address but no sense making it easier for them. :?


    I posted the Conditional Formatting solution. Do you want me to email that to you as well?


    I will also post the other solutions because someone else may benefit from looking at them when they have a similiar problem/dilemma.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Macro Inquiry - Beginner Support


    Here's the VBA approach you trying to get to.


    The button on the worksheet is the Forms toolbar's button and is a dummy graphic that sits there and does nothing until you attach a macro to it. It has not properties or events.


    Unlike the Conditional Formatting in Excel, with VBA case matters so I take the cell's contents, convert them to uppercase, and compare it to "YES".


    Anyway, I put in lots of comments but if you have any questions, post back.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!