none
Cell formatting as a function RRS feed

  • Question

  • hello guys, is it possible to create a function in vba for cell formatting?

    example, Col A10 Col B10, Col A11 Col B11

    Col A10 - filled with blue

    Col B10 - filled with green

    Col A10 to B11 -borders thick line

    How to create a function so it can be reused in other worksheets or workbook?

    Thanks for any ideas.


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Friday, April 21, 2017 2:56 AM

Answers

  • Hello,

    I don't think code would be shorter. If you want to set the Borders, we need to set its left/right/top/button.

    To create a function for multi-using, change Range("") into a variable, then we could call it in other subroutine.

    Sub test()
    Call Demo(Range("A10"), Range("B10"), Range("A10:B11"))
    End Sub

    Function Demo(cell1 As Range, cell2 As Range, cell As Range)

    End Function

    If you want to set these format in UDF, unfortunately, according to Description of limitations of custom functions in Excel, A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:
    Insert, delete, or format cells on the spreadsheet.

    To work around, please visit http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cguan Friday, April 28, 2017 5:47 AM
    Thursday, April 27, 2017 5:49 AM
    Moderator

All replies

  • Hi,

    This forum focuses on general questions related to Microsoft Excel. If you are looking for any VBA codes for your requirement, I suggest you can ask a question in Excel for Developers forum for more suggestion:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, April 24, 2017 1:07 PM
  • Thanks, Winnie.

    Are you able to move my thread to the correct forum? So, I will not waste extra bytes on the server space.



    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Tuesday, April 25, 2017 9:27 AM
  • Thanks, Winnie.

    Are you able to move my thread to the correct forum? So, I will not waste extra bytes on the server space.



    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    I have moved it. Hope you can find the solution there :)

    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, April 25, 2017 9:55 AM
  • Hello,

    You could format cells manually and record a macro at the same time, then you could get the code. For more information, please visit Automate tasks with the Macro Recorder

    You could create a macro using the following code in your workbook A.

    Then in workbook B, create a macro to use Application.Run ("'Path of workbook A'!Demo") to run the macro.

    Sub Demo()

    ' A10 - filled with blue

        Range("A10").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 12611584
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

    'B10 - filled with green Range("B10").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5287936 .TintAndShade = 0 .PatternTintAndShade = 0 End With

    'A10 to B11 -borders thick line Range("A10:B11").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 26, 2017 3:47 AM
    Moderator
  • Hi Celeste, thank you so much for your reply.

    I was just thinking if there's a way to create a function for formatting, so the code will be shorter.


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Thursday, April 27, 2017 4:09 AM
  • Hello,

    I don't think code would be shorter. If you want to set the Borders, we need to set its left/right/top/button.

    To create a function for multi-using, change Range("") into a variable, then we could call it in other subroutine.

    Sub test()
    Call Demo(Range("A10"), Range("B10"), Range("A10:B11"))
    End Sub

    Function Demo(cell1 As Range, cell2 As Range, cell As Range)

    End Function

    If you want to set these format in UDF, unfortunately, according to Description of limitations of custom functions in Excel, A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:
    Insert, delete, or format cells on the spreadsheet.

    To work around, please visit http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cguan Friday, April 28, 2017 5:47 AM
    Thursday, April 27, 2017 5:49 AM
    Moderator
  • Thanks, Celeste. :)

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Friday, April 28, 2017 5:46 AM