Applying a border to the same cells on every worksheet with VBA

  • I am trying to apply a thick border to the left side of the same cells on every worksheet given that the cell has a specific value. So if the cell says "P12" then apply a thick border to the left side of that cell and of the underneath cells to0, in my case C2:C7, otherwise apply a normal size border to the left side. It works only for the first sheet but doesn't apply to every sheet in the workbook. I haven't added any code for the thin border as I was just wanting to test it for the thick border to begin.


    I am new to VBA so I can't seem to see why it won't work. I may have made the code I have posted below more complicated that it needs to be. I have included the offset and this may not be necessary.


    This is my code so far:


    #



    #

  • Re: Applying a border to the same cells on every worksheet with VBA


    Based solely on the posted code, which isn't really clear, try this.


    The 2 main things that aren't clear are why a static range like 'C2:C7' to apply the formatting and this,

    Code
    Loop Until ActiveCell = .Range("C28")


    are you trying to loop until the active cell value equals the value in C28?

  • Re: Applying a border to the same cells on every worksheet with VBA


    The code you have doesn't seem to work. There are no error messages but no borders appear. The static range is because in the cell with P12 it is for the month December and then in the below cells it will have financial information which is only needed in the 5 cells below C2.


    So for example it will show P12
    £132
    £100
    £52
    £69
    £50
    I want to show a divide with a thick border to the left of the cell.


    I had it up to C28 as in C23 it also has P12 and I want to do the same for those cells but I was just testing out the first section to see if the code works.

  • Re: Applying a border to the same cells on every worksheet with VBA


    Eh, I know it doesn't work.:)


    Do you mean that wherever 'P12' is found in a cell you want to format that cell and the five cells below it?


    If you do then this might work.

    Code
    With rng.Resize(6).Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlMedium 
    End With


    By the way it still isn't clear where you want to loop to.

  • Re: Applying a border to the same cells on every worksheet with VBA


    Oh I am sorry, it is because you said 'try this'.


    Yes, so there is already a macro which then produces this cell and the cell either has P1-P12, so when it is P12 I want that cell and the cells underneath to have a thick border. Otherwise if it is P1-P11 then I want a thin border.


    I'm not sure if I need the offset rule. I would have used conditional formatting but it doesn't allow for a thick border.

  • Re: Applying a border to the same cells on every worksheet with VBA


    Try this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Applying a border to the same cells on every worksheet with VBA


    Excellent. Thank you so much.


    When I change the cell to say P1 it only puts the border on that cell as opposed to all the cells like you have done for the thick border with P12. How do I fix this?

  • Re: Applying a border to the same cells on every worksheet with VBA


    I have edited the code in Post# 6, try the edited code.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Applying a border to the same cells on every worksheet with VBA


    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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