Deselect a cell in a selected range using VBA

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Was hoping for something along theses lines, though the Cells.Deselct throws up a runtime error

Code:
For Each Cell In Selection
      If Cell.Font.ColorIndex = 5 Then
         Cell.Deselect
      End If
Next Cell
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
What version of Excel do you have? I have Excel 97, and deselect can only be used to change a selected chart to be a non-selected chart, and not be used on cell objects. Is that what causes the run-time error?
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Yeah It must be, the error states that deselct cannot be used on this type of object, any suggestions on how I can achieve this?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Let's stand back a bit first ... why do you want to do this? There may be other ways of achieving your aim. ( also, if your aim is just to do this type of selection, there are free add-in tools to do this for you )
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

OK, cheers Glenn, I have actually thought of another way around this problem, it isn't 100% automated but it is a LOT easier to program, the only problem this way is to add a cell to a multiple selection, do you know how to send ctrl and left mouse click from vba?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this:

Code:
Sub Test()
    Dim Rng As Range
    Set Rng = Range("A1")
    Set Rng = Union(Rng, Range("A3"))
    MsgBox Rng.Address
End Sub
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

Yp I'm trying the union approach but it's throwing up an error on rRange4, can u see why?...

Code:
 Set rRange2 = rRange1.SpecialCells(xlCellTypeConstants, 1)
             Set rRange3 = Cells(3, ValColLet)
              Set rRange4 = Union(rRange2, rRange3)
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
They are on the same sheet

Code:
Private Sub SelectConstants()
 
    Dim rRange1 As Range
    Dim rRange2 As Range
    Dim rRange3 As Range
    Dim rRange4 As Range
    Dim valX As Characters
              
  'Select Constants (multiple selection)
  
  
    Set rRange1 = Range(Cells(4, Me.ValColLet.Value), Cells(65536, Me.ValColLet.Value).End(xlUp))
        On Error Resume Next
            
            Set rRange2 = rRange1.SpecialCells(xlCellTypeConstants, 1)
             Set rRange3 = Range(Cells(3, ValColLet))
              Set rRange4 = Union(rRange2, rRange3)
      
    On Error GoTo 0
 
    If rRange4 Is Nothing Then
       MsgBox "No Data Found", vbExclamation
            Unload Me
                Exit Sub
    End If
      
     rRange4.Select
 
         Call CopyAndPaste
 
             rRange4.ClearContents
  
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What error do you get and where? I'm surprised you get one at all given On Error Resume Next.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,727
Messages
5,573,853
Members
412,554
Latest member
faridr
Top