CODEWITHSUNDEEP
vbaexcel
user9997971
user9997971

Reputation: 3

VBA: Check for odd/even numbers in a column and then check for correct value in another column

Macro Image of tableI could need your help with this one:

I have birth numbers (11 or 10 digits) in column D(9). And I have a column N(9) with sex (M and F). I want to check if the text in column N is correct based on the birthnumber. If the 3rd last digit in the birthnumber contain an even number then it's female. If the 3rd digit is an odd number it's a male. I want to use conditional formatting (mark the cell red) if M or F is wrong in column N.

Does anyone have a vba code for this? Thank you in advance.

Upvotes: 0

Views: 3379

Answers (2)

iAdesanyaDaniel
iAdesanyaDaniel

Reputation: 31

Check this out:

Option Explicit

Public Sub FormatConditionally()
    Dim i As Integer
    Dim birthNumbers As String
    Dim gender As String
    Dim genderCell As Range

    For i = 1 To Range("Table1").Rows.Count
        birthNumbers = Range("Table1[Birth number]")(i).Value
        Set genderCell = Range("Table1[F/M]")(i)
        gender = genderCell.Value

        If GenderIsValid(birthNumbers, gender) Then
            Call FormatAsValid(genderCell)
        Else
            Call FormatAsNotValid(genderCell)
        End If

    Next i

End Sub

Private Function GenderIsValid(birthNumbers As String, gender As String) As Boolean

    Dim lengthOfBirthNo As Integer
    Dim posOfGenderIdentifier As Integer
    Dim genderIdenfier As Integer
    Dim validGender As String

    lengthOfBirthNo = Len(birthNumbers)
    posOfGenderIdentifier = lengthOfBirthNo - 3

    genderIdenfier = Mid(birthNumbers, posOfGenderIdentifier, 1)

    If WorksheetFunction.IsEven(genderIdenfier) Then
        validGender = "F"
    Else
        validGender = "M"
    End If

    If (validGender = gender) Then
        GenderIsValid = True
    Else
        GenderIsValid = False
    End If

End Function

Private Sub FormatAsValid(rng As Range)
    rng.Interior.Color = RGB(255, 0, 0)
    'sets fill color to RED
End Sub
Private Sub FormatAsNotValid(rng As Range)
    rng.Interior.Color = RGB(255, 255, 255)
    'sets fill color to WHITE
End Sub

Don't mind the size of the code. The public subroutine (FormatConditionally) is what needs to be run, and the column name should remain as-it-is, in the attached caption(or, be changed as appropriate).

And also it works with the 3rd-last digit

I hope you would find it useful. Thank you.

Upvotes: 0

James Cooke
James Cooke

Reputation: 1293

Cant you just do this with an excel formula for example and reference the ID; =IF(ISODD(MID(D3,9,1)),"M","F")

if the 9th value in cell D3 is odd then Male, else Female

Upvotes: 1

Related Questions