Format painter - any way to make it set focus to *END* of selected area? RRS feed

  • Question

  • If I select a row and click the "Format Painter", then select 100 more rows, I would like the focus to be set at the *END* of the new selection rather than the beginning.

    Does this require a macro? I *never* want it to go to the beginning.


    Thursday, September 18, 2014 5:25 PM

All replies

  • Your objective would certainly require VBA code though more than a simple macro.

    If you simply want to activate the last cell (of the first area if multiple areas selected)  however selected the following should work, whether using the format painter or otherwise. Paste the following into the ThisWorkbook module

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        If Target.Rows.Count > 10 Then
            Target(Target.Rows.Count, Target.Columns.Count).Activate
        End If
    End Sub

    The 10 is just for testing, change to your 100

    However if you only want to activate the last cell only if using the format painter its a lot more complicated

    Thursday, September 18, 2014 9:24 PM
  • Even though it is a lot more complicated, I *would* like it to activate the last cell only when using the format painter.

    I don't understand why Microsoft would code it this way. Am I the only one who doesn't like this behavior?

    I use it almost every week and constantly wish it were the other way.

    I am comfortable with VB code and have no problem assigning the code to another button on the toolbar in lieu of the format painter.

    Can you help?

    Friday, September 19, 2014 11:17 AM
  • Did you try the event code I posted to test if that's the behaviour you are looking for with the format painter?

    It really is quite complicated to make it work only with the format painter, it would take a while to develop, something along these lines: 

    On the Ribbon Home tab hide the original Clipboard group and make copy of the group but with Format Painter a custom toggle bar (are you familiar with the xml ?). When the toggle is pressed preset the format painter to run and set a flag, in the sheetchange what will get triggered if the flag is set apply the code I posted previously and reset to toggle and flag

    I can see one or two problems, such has how to reset the toggle and flag if the format painter has gone out of scope (eg user presses Esc). It should be possible to put something together that works reasonably well most of the time, but it would take a lot of work to totally reliably replicate the original format painter.

    Sunday, September 21, 2014 10:15 AM