Flag if column value meets criteria Excel VBA

[JimLes]

I am trying to build a message box flag if the row count is over 3 which works AND if the person's age is over 40 in colEcell. It recognizes the value in colEcell but it pops up the flag no matter what. I just want it to pop up if the used row count is over 3 and if anyone in colEcell is over 40.

Any ideas where my code is flawed?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim colAcell As Range, colBcell As Range, colCcell As Range, colDcell As Range, lastRow As Range
Dim wks As Worksheet
Set wks = Worksheets("DataEntry")
Set colAcell = wks.Range("AG1")
Set lastRow = colAcell.SpecialCells(xlCellTypeLastCell)



Do Until colAcell.Row > lastRow.Row
Set colBcell = colAcell.Offset(0, 1)
Set colEcell = colAcell.Offset(2, 32)



If WorksheetFunction.CountA(Range("A1", Range("A" & Rows.Count).End(xlUp))) > 3 And (colEcell.Value > 39) Then
Cancel = True
MsgBox "Please ensure to give letter to employee."
'MsgBox colEcell.Value
End
End If
Set colAcell = colAcell.Offset(1)
Loop

 

[SkipVought]

Your code is really difficult to follow.

Why no headings? Tables should have headings! Most usually, data begins in row 2.

dim r as range

for each r in range([AG1], [AG1].end(xldown))
   if r.row > 3 and intersect([E1].entirecolumn, r.entirerow).value > 39 then

   end if
next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JimLes]

Well, I think I am on the right track bu can't get both my Ifs to work together. If I take the RowCount out, it will flag those over 40 but when I put the over if for the row count more than 3, it won't work.
Any help will be greatly appreciated.

Dim RowCount As Long
Dim cel As Range
RowCount = WorksheetFunction.CountA(Range("A1", Range("A" & Rows.Count).End(xlUp)))
'MsgBox "The number of rows is " & RowCount
If RowCount > 3 Then
For Each cel In Range("BM3:BM500")
If cel.Value > 40 Then MsgBox "Please ensure to give employees Notice with their Letter."
Exit For
Next
End If

 

[SkipVought]

Suppose you explain in prose rather than code and cells, what you are trying to accomplish.

You also need to explain the structure of your sheet and what that all means.

A representative sample of the data on your sheet would also be helpful.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JimLes]

Skip, thanks as always for your help. I can't believe I did not realize this earlier. My original code works. In my formula to calculate the age, I used an If Then that put " " in the cell if there was not birthdate populated. I changed it to be 0 instead and the code worked perfectly. I just used custom format to hide the zeros and problem solved.

Thanks so much!!