Trying to assign a value to cells based off of predetermined criteria

here is what I have currently, however I need it to find if column D contains "Mr" or "Mrs" anywhere in the cell which this code does not do. Another problem is that my macro replaces the value in Column D instead of placing it in Column C.

Sub Cosignee_identity()
Dim ws As Worksheet
Dim cell As Range
Dim myrng As Range
Dim lastCell As Range

Set ws = ThisWorkbook.Sheets("Sheet1")
Set myrng = ws.Range("D2:D" & Range("D" & ws.Rows.Count).End(xlUp).Row)
With myrng
Set lastCell = .Cells(.Cells.Count)

For Each cell In myrng
If Application.WorksheetFunction.CountIf(myrng, cell) < 250 Then
If cell.Value <> "Mr" Or cell.Value <> "Mrs" Then
cell = "Ts"
Else
cell = "Id"
End If

Else
cell = "Bi"

End If

Next

End With

Hello sclog,

Welcome to the forum!
In cell C2 and copied down, give this formula a try:

Please Login or Register  to view this content.

Thank you for replying tigeravatar. This formula worked for Ts, and Bi, which was a huge help. But it doesn't seem to work for the cells labeled Id.

I was thinking about using the Instr function in my macro to help with that. Do you think that could solve my issue? Or should I just add more to the formula you provided?

EDIt: Nevermind I am clearly wasn't paying attention it works fine. Thank you TigerAvatar.