EXCEL VBA, IF odd and even with condition

[xdenama]

Sub OddRowAlert()
With Range("B16:B100")
.Formula = "=IF((MOD(ROW(B16),2)),"YES","NO")"
.Formula = .Value
End With
End Sub

I get this code in another forum, but I don't know how to give condition, example if "A16" is not empty than "B16" can show either "YES" or "NO". If "A16" is empty, than nothing to show?

 

[drkjz]

Welcome! I'm new to the forum as well but I think I can answer your question. The function ROW(B16) returns the row number "16". The mod() function takes the first number (16) and divides it by the second number (2) and returns the remainder. For an even number, the function will return 0 or FALSE and hence give you the "NO". An odd number returns a value of 1 or TRUE and gives the "YES". The line of the code runs irrespective of what values are in the range of B16:B100 since it based on the row number. I often use a similar function MOD(ROW(),2) or MOD(ROW()+1,2) with conditional formatting to selectively fill rows in a big table to ease readability. The choice of the 2 MOD functions depends on whether I want the even or odd colors formatted.

 

[xdenama]

Welcome! I'm new to the forum as well but I think I can answer your question. The function ROW(B16) returns the row number "16". The mod() function takes the first number (16) and divides it by the second number (2) and returns the remainder. For an even number, the function will return 0 or FALSE and hence give you the "NO". An odd number returns a value of 1 or TRUE and gives the "YES". The line of the code runs irrespective of what values are in the range of B16:B100 since it based on the row number. I often use a similar function MOD(ROW(),2) or MOD(ROW()+1,2) with conditional formatting to selectively fill rows in a big table to ease readability. The choice of the 2 MOD functions depends on whether I want the even or odd colors formatted.

Thank for reply, I'm asking about condition at another column, "A16", range for write the answer is "B16" and so on.

 

[drkjz]

Sorry, I misunderstood your question. I believe this code would do what you are asking. Note that the first part just writes the result and the second would leave the formula in the range

Sub firstColAlert()
    With Range("B16:B100") ' just writes the result in the range
    .Formula = "=IF(ISBLANK(A16),"""",""Not empty"")"
    .Formula = .Value
    End With
    
    With Range("D16:D100") ' writes the formula the range
    .Value = "=IF(ISBLANK(A16),"""",""Not empty"")"
    End With
    
End Sub

 

[xdenama]

Sorry, I misunderstood your question. I believe this code would do what you are asking. Note that the first part just writes the result and the second would leave the formula in the range

Sub firstColAlert()
    With Range("B16:B100") ' just writes the result in the range
    .Formula = "=IF(ISBLANK(A16),"""",""Not empty"")"
    .Formula = .Value
    End With
  
    With Range("D16:D100") ' writes the formula the range
    .Value = "=IF(ISBLANK(A16),"""",""Not empty"")"
    End With
  
End Sub

Thanks again, but not consider odd and even row...

 

[drkjz]

So you want every other row to evaluate whether the first column has a value? Then, you need to just nest the IF statements

Sub firstColAlert()
    With Range("B16:B100") ' just writes the result in the range
    .Formula = "=IF(MOD(ROW(B16),2),"""",IF(ISBLANK(A16),"""",""Not empty""))"
    .Formula = .Value
    End With
    
    With Range("D16:D100") ' writes the formula the range
    .Value = "=IF(MOD(ROW(B16),2),"""",IF(ISBLANK(A16),"""",""Not empty""))"
    End With
    
End Sub