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

 

[brian.wethington]

Loops are valuable in many cases, but in general if you are able to avoid them then you should probably avoid them (they are incredibly inefficient). In this case I am using the native autofilter feature in excel to create only ranges that need to be worked with and affecting those ranges (for the font color). The other scenario you are needing to implement is making a formula change to a range which can be done effectively without looping in general.

As for the error, I went crazy with the cells statements and mixed 2 methods sorry:

Set Rng = WS.Range(WS.Cells(1, 1), WS.Range("AE" & Cells(Rows.Count, 11).End(xlUp).Row))

 

[lcwhite]

Thanks Brian! I'm really learning a lot from you . You will notice in my If condition I have If (AE < 1.5) AND (K > 0 OR L > 0) THEN Highlight Red. Your code has 2 separate steps, which doesn't give me the result I need.
Rng.AutoFilter Field:=31, Criteria1:="< 1.5", Operator:=xlAnd
Rng.AutoFilter Field:=11, Criteria1:="> 0", Operator:xlOr Rng.AutoFilter Field:=12, Criterial1:=">0"
Set Rng2 = Intersect(Rng, Rng.Offset(1))
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed Is this what I need to do? Also why can't I format anything on this forum? Seems others can.....

 

[xrichardx]

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.

Try This:

Populate as well the Cell number with Loop Variable (If you still prefer to use it).

ActiveCell.Formula = "=IFERROR(+IF(+K" & i & "=0,0,+R" & i & "/(+IF(+K" & i & ">L" & i & ",K" & i & ",L" & i & ")*$AE$1/365)/P" & i & "),0)"

Cheers
Rich

 

[brian.wethington]

Thanks Brian! I'm really learning a lot from you . You will notice in my If condition I have If (AE < 1.5) AND (K > 0 OR L > 0) THEN Highlight Red. Your code has 2 separate steps, which doesn't give me the result I need.
Rng.AutoFilter Field:=31, Criteria1:="< 1.5", Operator:=xlAnd
Rng.AutoFilter Field:=11, Criteria1:="> 0", Operator:xlOr Rng.AutoFilter Field:=12, Criterial1:=">0"
Set Rng2 = Intersect(Rng, Rng.Offset(1))
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed Is this what I need to do? Also why can't I format anything on this forum? Seems others can.....

There is no technical way to in 1 step perform an or on 2 different fields using autofilter (this can be done in advanced filtering), however, using the 2 steps I used has in essence done the same thing as a literal AE<1.5 AND (K>0 OR L>0). Now if you are not getting the expected results then perhaps we need to determine exactly what you are desiring.

Rng.AutoFilter Field:=31, Criteria1:="<1.5"
Rng.AutoFilter Field:=11, Criteria1:=">0"
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed

Finds all instances where AE<1.5, and K>0 and changes the font color to red on those found cells in column AE

Rng.AutoFilter Field:=31, Criteria1:="<1.5"
Rng.AutoFilter Field:=12, Criteria1:=">0"
Rng2.Columns(31).SpecialCells(xlCellTypeVisible).Font.Color = vbRed

Finds all instances where AE< 1.5, and L>0 and changes the font color to red on those found cells in column AE

Doing this in 2 steps or 1 step as far as I understand your needs should not change the results (just have to remember to reset the autofilter between both steps).

If you are having problems formatting your posts you may try using the Go Advanced button that is available under your post. You can use code and html tags to keep alignments, as well as font colors, font styles and things of that nature (for my normal post quick reply option some of the options are available, but it may be a setting; I am not really a good forum user).

 

[brian.wethington]

Try This:

Populate as well the Cell number with Loop Variable (If you still prefer to use it).

ActiveCell.Formula = "=IFERROR(+IF(+K" & i & "=0,0,+R" & i & "/(+IF(+K" & i & ">L" & i & ",K" & i & ",L" & i & ")*$AE$1/365)/P" & i & "),0)"

Cheers
Rich

You also would have to either update the active cell or use another method to loop the row you are add the formula to, at least I didn't see where the activecell was being updated. For example:

Sub Macro2()
Dim WB As Workbook, WS As Worksheet, Rng As Range, CE As Range

Set WB = Workbooks("Sample.xlsx")
Set WS = WB.Sheets(1)
Set Rng = WS.Range("AE2", WS.Cells(Rows.Count, 11).End(xlUp).Offset(, 20))
For Each CE In Rng
    CE.Formula = "=IFERROR(IF(K" & CE.row & "=0,0,R" & CE.row & "/(F(K" & CE.row & ">L" & CE.row & ",K" & CE.row & ",L" & CE.row & ")*$AE$1/365)/P" & CE.row & "),0)"
    If CE.Value < 1.5 And (CE.Offset(, -20).Value > 0 Or CE.Offset(, -19).Value > 0) Then CE.Font.Color = 255
Next
End Sub

 

[xrichardx]

Yes Brian, I agree .

Thanks
Rich

 

[lcwhite]

Rng2.Columns(31).Formula = "=IFERROR(IF(K2=0,0,R2/(IF(K2>L2,K2,L2)*$AE$1/365)/P2),0)"
This is working great for me. Now I want to change the value of the error from 0 to "NO USAGE" and ROUND the output of the formula by 2. Would it look like this: Rng2.Columns(31).Formula = "=IFERROR(ROUND(IF(K2=0,0,R2/(IF(K2>L2,K2,L2)*$AE$1/365)/P2),2),"NO USAGE")"