Announcement

Collapse
No announcement yet.

Multiple Formatting Based On Specific Cell Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multiple Formatting Based On Specific Cell Value



    Dear All,

    Being a beginner in the world of vba, I am slightly stuck in finding a workable solution to my problem and I was wondering if someone could help me with the following.

    I am trying to write a macro that will perform several formatting options to cells, which contain a specific value. I have attached the macro that I have at the moment, which works fine if you are only going to perform a single formatting option, however I am looking to do various other formatting.

    I have a worksheet that will contain various titles which will alway be located in Column A. The title name will only ever appear once, however the number of titles will change.

    What I would like to do is format that Title cell and 10 columns after it and add a row above it and below it and shade them and to reduce the height of the added rows.

    For ease of understanding, I have created a macro called “colourgroup” which show what I would like to happen to the cell that contains the specific cell values.

    I tried using the find method, however runtime errors occur if there are no cell found.

    Any help would be appreciated.

    For a workable solution, which i think would be pretty simple for someone who has a good knowledge base of vba to create a macro very easily. I would be happy to pay £3.00 GBP.
    I dont know if this amount is fair or not but need to start the ball rolling. I can also make payment via paypal.

    There is potentially more queries that i may put forward dependant upon success of this one.

    Thanks

    BT
    Attached Files

  • #2
    Re: Multiple Formatting Based On Specific Cell Value

    Hello BT

    That's an interesting problem to start programming VBA!

    While reading this, I was first thinking about conditional formatting and doing this without any macro. Then I realized (upon viewing the attached document) you also want to insert rows. Then you're stuck with conditional formatting.

    As a beginning programmer, you might want to take advantage of a number of pages that Dave Hawley created, on how to speed things up. If column A contains quite some blank cells and is quite lengthy, try using SpecialCells(xlCellTypeConstants) to only look among those cells containing constants (and hence exclude blank cells).

    In addition, given that you have the macro "colorgroup", pay attention to using parameters to be passed on to procedures. This makes your code more general / reusable / split into subparts, each subpart serving only 1 purpose.

    Lastly, be careful since the Find method recalls the last used settings if you don't expressly add them as arguments to the Find method. Search on the main Ozgrid website for more detailed explanations. That will probably be more helpful than someone here providing the code.

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: Multiple Formatting Based On Specific Cell Value

      Hi Wigi,

      Thank you for your advice and for pointing me in the right direction.

      BT

      Comment


      • #4
        Re: Multiple Formatting Based On Specific Cell Value

        Hi

        Post the progress you make here in this topic, than we can keep an eye on it and give extra instructions.

        The title name will only ever appear once, however the number of titles will change
        The why do you loop within your Find statements? It only occurs once.

        Good luck with it,

        Wigi
        Regards,

        Wigi

        Excel MVP 2011-2014

        For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

        -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

        Comment


        • #5


          www.jormabridal.com RUINS another wedding

          If you want your wedding ruined and get 1000's of spam emails a day, see http://www.jormabridal.com/

          Comment

          Working...
          X