code to format cells according to variables

  • hello from Montréal


    I have a Worksheet in wich I have 5 différents variables (in cells E61 to E65)


    I would like VBA to recognise the value in those cells and format le nomber of cells coresponding in different lines


    say E61 value is 10


    I want VBA to format the next 10 cells with borders, for all lines with "10" in colombs "B", starting in colomb "G"


    the attached file should be more explicit, you have the blanck sheet and the result wanted sheet


    thanks in advance for your help

  • Re: code to format cells according to variables


    You can do the formatting with Conditional Formatting so no VBA is needed.


    Select cell G5 and choose New Rule under Conditional Formatting. Choose to use a formula and use this formula:


    =COLUMN(G5)-6<=$B5


    In the rule window, add a border around the cell for the formatting piece and close the window. Use the Format Painter to copy the Conditional Formatting to the rest of the cells.


    Using this method does not require the E61-E65 cells as long as the B column has the number of cells that need a border.