Loop with conditional IF statement

[lcwhite]

Hey Guys - I'm pretty new to these macros and I can't see to record what I'm trying to do. Here is the scenario: COL K COL L COL AE
0 4 -133
0 1 12
3 0 1
2 0 1.5
0 0 1
0 0 342
IF COL AE is < 1.5 & (K > 0 or L > 0) THEN Highlight COL AE in red(255). I need to Loop thru COL AE as long as there is data in K. I tried to record this with conditional formatting, but just couldn't get it to work. Thanks so much! Lori

 

[lcwhite]

Column K Column L Column AE
0 4 -133
0 1 12
3 0 1
2 0 1.5
0 0 1
0 0 342

 

[xrichardx]

Column K Column L Column AE
0 4 -133
0 1 12
3 0 1
2 0 1.5
0 0 1
0 0 342

Please run this macro.

Private Sub Macro1()
LastRow = Worksheets("Sheet2").Cells(Rows.Count, "K").End(xlUp).Row
    For i = 2 To LastRow
        
        If (Worksheets("Sheet2").Range("AE" & i).Value < 1.5) And _
            ((Worksheets("Sheet2").Range("K" & i).Value > 0) Or (Worksheets("Sheet2").Range("L" & i).Value > 0)) Then
            
            Worksheets("Sheet2").Range("AE" & i).Font.Color = vbRed
            
        End If
    
    Next i
End Sub

Cheers
Rich

 

[brian.wethington]

This can be done without a loop:

Sub Macro1()
Dim WB As Workbook, WS As Worksheet, Rng As Range, Rng2 As Range

Set WB = Workbooks("Sample.xlsx") ' set this to the already opened workbook
Set WS = WB.Sheets(1) ' set this to the index sheet desired
Set Rng = WS.Range(WS.Cells(1, 1), WS.Cells(WS.Cells(Rows.Count, 31).End(xlUp).Row, WS.Cells(1, Columns.Count).End(xlToLeft).Column))

Rng.AutoFilter Field:=31, Criteria1:="<1.5", Operator:=xlAnd
Rng.AutoFilter Field:=11, Criteria1:="<>"
Set Rng2 = Intersect(Rng, Rng.Offset(1))
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed

Rng.AutoFilter
Rng.AutoFilter Field:=31, Criteria1:="<1.5", Operator:=xlAnd
Rng.AutoFilter Field:=12, Criteria1:="<>"
Set Rng2 = Intersect(Rng, Rng.Offset(1))
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed
Rng.AutoFilter

End Sub

 

[brian.wethington]

For whatever reason I set my 2nd criteria to non empty instead of zero, the fix for that is simple on these 2 lines:

Rng.AutoFilter Field:=11, Criteria1:="<>"
Rng.AutoFilter Field:=12, Criteria1:="<>"

Rng.AutoFilter Field:=11, Criteria1:=0
Rng.AutoFilter Field:=11, Criteria1:=0

Sorry for overlooking that.

 

[lcwhite]

You guys are awesome! Thanks!

 

[lcwhite]

This formula works perfectly populating rows AE2:AE3495 with my results.
Range("AE2:AE3495").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
However, it seems better logic to stick this formula inside a Loop as I don’t always know the number of rows I will have.
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To LastRow
ActiveCell.Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _
((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then
Worksheets("Sheet1").Range("AE" & i).Font.Color = 255

End If
Next i
My syntax is now wrong, as I’m only populating the first cell with my formula. What am I missing?? Thanks you guys!!! I’m really new to this.

 

[brian.wethington]

It would definitely be best to not loop at all in your code

Sub Macro1()
Dim WB As Workbook, WS As Worksheet, Rng As Range, Rng2 As Range

Set WB = Workbooks("Sample.xlsx") ' set this to the already opened workbook
Set WS = Sheets(1) ' set this to the index sheet desired
Set Rng = WS.Range(WS.Cells(1, 1), "AE" & WS.Cells(WS.Cells(Rows.Count, 11).End(xlUp).Row, WS.Cells(1, Columns.Count).End(xlToLeft).Column))
Set Rng2 = Intersect(Rng, Rng.Offset(1))
Rng2.Columns(31).Formula = "=IFERROR(IF(K2=0,0,R2/(IF(K2>L2,K2,L2)*$AE$1/365)/P2),0)"
Rng.AutoFilter Field:=31, Criteria1:="<1.5", Operator:=xlAnd
Rng.AutoFilter Field:=11, Criteria1:=">0"
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed
Rng.AutoFilter
Rng.AutoFilter Field:=31, Criteria1:="<1.5", Operator:=xlAnd
Rng.AutoFilter Field:=12, Criteria1:=">0"
Set Rng2 = Intersect(Rng, Rng.Offset(1))
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed
Rng.AutoFilter

End Sub

I am unsure about your formula, so it was edited slightly, but you can always copy it precisely as needed in place.

 

[lcwhite]

This code gives me the following error: Run-time error '1004': Method of "Range" of object_Worksheet' failed on the Set Rng = WS. Range(WS.Cells(1,1)..... By the way, why am I unable to use formatting why I type these replies? I see others are able to...

 

[lcwhite]

As an "old-time" programmer, I prefer to use the loop - as I understand this terminology. Just trying to learn VBA and on the fly and it seems what you are doing is a little Greek to me. This is a great forum for us newbies to VBA! Thanks to everyone for the help!