MsbBox code help

[Muckibus]

Hello, can one of you geniuses tell me the code I need to do the following:

I want a message box to pop up when someone places an "A" or and "a" in the same column in a spreadsheet.

So for example, if there is a "A" in B5 and a an "A" in B18 I want a message to pop up...

can someone tell me the code to put in worksheet change...

I would really appreciate it, thank you so much in advance.

[GTO]

This would provide a case-insensitive search for duplicates in the target column, if that meets needs? If you need it to limit looking for duplicates only to "A" or "B" or whatever, where would we keep the list of values to check, on a worksheet?



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Count > 1 Then
If Not Target.Value = vbNullString Then
If Application.CountIf(Me.Range(Me.Cells(2, Target.Column), RangeFound(Me.Columns(Target.Column))), Target.Value) > 1 Then
MsgBox "There are (" & Application.CountIf(Me.Range(Me.Cells(2, Target.Column), RangeFound(Me.Columns(Target.Column))), Target.Value) & _
") occurrences of the value " & Target.Value & " in column " & Target.Column & ".", _
vbInformation Or vbOKOnly, _
vbNullString
End If
End If
End If
End Sub

Private Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False _
) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

Hope that helps,

Mark

[Kenneth Hobs]

Welcome to the forum!

Why not use Data Validation and =CountIf()?