**Macro with IF conditions**

[lhk201]

Is it possible to create a macro that follows the following conditions?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
If any cell within the range a6:h200 is selected then that cell will have the word “no” entered into it. This should happen only when the cell is blank.<o></o>
<o> </o>
If the cell is not blank and it is selected within the same range then the cell should now be cleared.<o></o>

Thanks in advance for any help at all regarding this matter.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A6:H200")) Is Nothing Then Exit Sub
If Target.Value = "" Then
    Target.Value = "no"
Else
    Target.ClearContents
End If
End Sub

 

[MrKowz]

Try:

Public Sub lhk201()
If Not Intersect(ActiveCell, Range("A6:H200")) Is Nothing Then
    If ActiveCell.Value = "" Then
        ActiveCell.Value = "No"
    Else
        ActiveCell.ClearContents
    End If
End If
End Sub

 

[lhk201]

Hello!

Thanks for the very prompt response!

Neither of these currently seem to work. I am entering it into Module 1 is that the correct place?

Thanks

 

[lhk201]

Ah it works when I actually run the macro, is there a way to make it automatic?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

See above.

 

[VoG]

Ah it works when I actually run the macro, is there a way to make it automatic?

My code will run automatically if you put it in the worksheet's code module.

 

[lhk201]

Ah thanks VOG it works! I got a bit excited and jumped the gun there.

Just one more question, when I highlight a group of cells within that range the macro there is a runtime error 13 type mismatch.

Is there a way of disabling selecting a group of cells?

 

[VoG]

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A6:H200")) Is Nothing Then Exit Sub
If Target.Value = "" Then
    Target.Value = "no"
Else
    Target.ClearContents
End If
End Sub

 

[lhk201]

Perfect, thanks very much.

One more thing! If I wanted the range to be a6:h200, a1:b3 and j4:j9 for example?

Thank you!!!