Excel record previous selection 1

[guitardave78]

Hi there

I want to be able to click on a cell and have that cell format the previous selection.

Can this be done (dont want to get into a user form yet!!)

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[xlbo]

you would need to use the SELECTION CHANGE event of the worksheet

Store the Previous address in a public variable and compare it to the new address using the TARGET object. How would you trigger this event though ?? presumably you wouldn't want it to happen for EVERY cell you select ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[guitardave78]

Basically i have a few cells on the right that have a particular format. I want to make a selection of other cells, then click on one of the formatted cells and have that format applied to the selection.

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[xlbo]

and how will excel know that ??

why not just use

Ctrl+C (copy)

Alt + e > s > t (paste special > formats)

Other than that you will need to check the TARGET object for every single selection that you make. If it INTERSECTS with a cell in your "copy formats" range, you would need to refer to your public variable (holding the previous selected range) and then code the paste special there...

<pseudocode>

Worksheet Selection Change event

dim OldSelection as range
dim NewSelection as range

If OldSelection is nothing then
  OldSelection = target
  NewSelection = Target
else
  OldSelection = NewSelection
  NewSelection = Target
end if

if NewSelection exists in the "formatting range" then
  Copy NewSelection
  Paste Special > Formats into OldSelection
else
end if

</pseudocode>

Let me know if you have any specific problems

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[guitardave78]

Cool, sorted!!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If OldSelection Is Nothing Then
      Set OldSelection = Target
      Set NewSelection = Target
    Else
      Set OldSelection = NewSelection
      Set NewSelection = Target
    End If

    'MsgBox OldSelection & " " & Target
    Dim a As Range
    If Target.Cells.Count > 1 Then Exit Sub
    
        If Not Intersect(Target, Range("G1:G100")) Is Nothing And OldSelection.Cells.Column < 6 And Intersect(OldSelection, Range("G1:G100")) Is Nothing And Target <> "" Then
            Target.Select
            Selection.Copy
            OldSelection.Select
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
            Selection.Cells(, 4) = Target
            Application.CutCopyMode = False
        End If
      end if
end funciton

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[guitardave78]

end sub even

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[xlbo]

nice - thanks for posting the solution back

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[guitardave78]

Just thought i would paste a version that works!!

Dim OldSelection As Range
Dim NewSelection As Range
Public Sub Worksheet_SelectionChange(ByVal Target As Range)

    If OldSelection Is Nothing Then
      Set OldSelection = Target
      Set NewSelection = Target
    Else
      Set OldSelection = NewSelection
      Set NewSelection = Target
    End If

    'MsgBox Target.Cells.Count
    Dim a As Range
    If Target.Cells.Count > 1 Then Exit Sub
    '"G37:G39" is the range where you have the template cells you wish to copy the formatting of
        If Not Intersect(Target, Range("G37:G39")) Is Nothing And Intersect(OldSelection, Range("G37:G39")) Is Nothing And Target <> "" Then
            Target.Select
            Selection.Copy
            OldSelection.Select
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
            'Selection = Target
            Application.CutCopyMode = False
        End If
End Sub

}...the bane of my life!

http://www.fuzzyd.co.uk