prevent selection highlighted cells

[abdelfattah]

hi experts

I no know if what I'm asking it , it's possible I would prevent select any cell is highlighted if add message box " you can't select highlighted cells" it will a great

note : if I select the cells are highlighted and locate within them empty cell or not color cells it should prevent after show message should select the cells are filled without contain color

a.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
4
5									FFFF
6		AA			BB						FF
7								AA
8													DD
9			DD		AA			AA
10
11						DD						DD
12			CC				DD
13
14						FF			FF
15														FFF
16
DD

 

[Joe4]

if that is the case try this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
tr = Target.Rows.Count
If Target.Count > 1 Then
 For Each cell In Target
   If cell.Interior.Pattern > 0 Then
   MsgBox ("you can't select highlighted cells")
   Target.Offset(tr + 1, 0).Select
   Exit For
   End If
 Next cell
Else
    If Target.Interior.Pattern > 0 Then
     Target.Offset(1, 0).Select
     MsgBox ("you can't select highlighted cells")
    End If
End If
End Sub

Note that if they select All cells, that will result in an overflow error.

If the answers to my previous questions are "Yes", this code will avoid that issue.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ct As Double
    Dim cell As Range
    
'   Count cells selected
    On Error GoTo err_chk
    ct = Selection.Count
    On Error GoTo 0
    
    For Each cell In Target
        If cell.Interior.Pattern > 0 Then
            cell.Offset(1, 0).Select
            MsgBox ("you can't select highlighted cells")
            Exit For
        End If
    Next cell
    
    Exit Sub
    
err_chk:
    If Err.Number = 6 Then
        Range("A1").Select
        MsgBox "You cannot select all cells"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[Joe4]

@offthelip it doesn't work with condition formatting

That is why I asked the question.
Manual formatting and conditional formatting work differently, and are identified differently in VBA.

 

[abdelfattah]

@Joe4 that's great but I note it doesn't work with condition formatting

 

[abdelfattah]

That is why I asked the question.
Manual formatting and conditional formatting work differently, and are identified differently in VBA.

so it's impossible do that together

 

[Joe4]

so it's impossible do that together

I never said that. I said it is "different".

We should be able to use the same conditions you are using in your conditional formatting in the VBA code.
What is your Conditional Formatting rule?
Are there more than one?
Are they always the same?

 

[Fluff]

To work with CF you can change Joe's code like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ct As Double
    Dim cell As Range
    
'   Count cells selected
    On Error GoTo err_chk
    ct = Selection.Count
    On Error GoTo 0
    
    For Each cell In Target
        If cell.DisplayFormat.Interior.Pattern > 0 Then
            cell.Offset(1, 0).Select
            MsgBox ("you can't select highlighted cells")
            Exit For
        End If
    Next cell
    
    Exit Sub
    
err_chk:
    If Err.Number = 6 Then
        Range("A1").Select
        MsgBox "You cannot select all cells"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[abdelfattah]

I never said that. I said it is "different".

We should be able to use the same conditions you are using in your conditional formatting in the VBA code.
What is your Conditional Formatting rule?
Are there more than one?

the rule is =ISNUMBER(FIND("TOTAL",$O1))
now there is no more than one

Are they always the same?

I don't understand it

 

[abdelfattah]

@Fluff that's awesome , can you mod after show the message then select the filled cells without highlighted as post#9

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim cell As Range, Rng As Range
   Dim Flg As Boolean
  
   For Each cell In Target
      If cell.DisplayFormat.Interior.Pattern > 0 Then Flg = True
      If cell.DisplayFormat.Interior.Pattern = -4142 And cell.Value <> "" Then
         If Rng Is Nothing Then Set Rng = cell Else Set Rng = Union(Rng, cell)
      End If
   Next cell
   If Not Rng Is Nothing Then
      Rng.Select
   ElseIf Flg Then
      Range("A1").Select
   End If
   If Flg Then MsgBox "you can't select highlighted cells"
End Sub

 

[abdelfattah]

@Fluff thanks very much for your answering