Excel/VBA to format entire row Dynamically RRS feed

  • Question

  • Hi All,

    I have 2 workbooks WB1 & WB2.  In WB1 there are nearly 100 name ranges.  Each of these name ranges are formated with either general, number, Percentage (with 1 or 2 decimal places) and date format.

    In WB2, Sheet1 from A2 to A101 i have all these names listed which are in wb2.

    I want to write a code which will

    1. loop through A2:A100 in wb2 sheet1 and
    2. for each names it will go to WB1 in the sheet where that name is defined
    3. copy format of that name range cell
    4. change entire row in wb2 sheet1 with that format

    for example

    1. If A2 in wb2 sheet1 has text "MY_NAME_2" then
    2. Go to "MY_NAME_2" name in wb1 and copy the format of that cell. If it is percentage then
    3. change entire row of A2 in wb2 sheet1 to percentage



    Tuesday, August 19, 2014 7:45 PM

All replies

  • Hi Zaveri,

    Which problem did you come across? As far as I know, we can use Range(“RangeName”).Copy the target cell. Then we can use the Range.EntireRow.PasteSpecial xlPasteFormats to past format from the previews range we copied.

    Also links below may be helpful for you learning Excel developing:

    How do I... (Excel 2013 developer reference)
    Object model reference (Excel 2013 developer reference)

    Best regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 20, 2014 7:48 AM