Trying to script the selection of every other column from "F" to "X" looping through rows 3 thru 70

[rdomingue]

My objective is to select cells on row for columns F thru X and have conditional formatting comparing to cell on same row in column z. I need to loop through row 3 thru 70.

Sub FindLowestCost()
Dim i As Integer
For i = 3 To 70
Range("F3,H3,J3,L3,N3,P3,R3,T3,V3,X3").Select '****this line is my problem. don't know format inside ()
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=Cells(i, 26).Value
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16753384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Next i
End Sub



My problem is in the selection: Something like this below doesn't appear to work. It loops through but the conditional format doesn't stick:
Sub FindLowestCost()
Dim i As Integer
For i = 3 To 70
Cells(i, 6).Select Cells(i, 8).Select
Cells(i, 10).Select
Cells(i, 12).Select
Cells(i, 14).Select
Cells(i, 16).Select
Cells(i, 18).Select
Cells(i, 20).Select
Cells(i, 22).Select
Cells(i, 24).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=Cells(i, 26).Value
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16753384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Next i
End Sub



Thanks for the suggestions. Randy

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Try this:

Sub FindLowestCost()
    Dim i As Long
    'Cells.FormatConditions.Delete
    For i = 6 To 24 Step 2
        With Cells(3, i).Resize(68)
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                Formula1:="=" & Range("Z3").Address(False, True)
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Font
                .Color = -16753384
                .TintAndShade = 0
            End With
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13561798
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
    Next
End Sub

If you need to run the macro more than once I have left the statement that deletes all the conditional formatting at the start of the code but commented out.
I step through from column 6 to column 24 in steps of 2 so it misses every other column.
With Cells(3, i).Resize(68) starts at row 3 column i and increases the number of rows in the range to 68. That will take it to row 70.
The formula is a bit tricky. If you were to type it into the conditional formatting dialog you would put: =$Z3
So I concatenate "=" with the address. The (False, True) controls where the "$" goes.

I think the rest is your code.

regards,

 

[Peter_SSs]

Randy

You shouldn't need to select anything or loop through either columns or rows.
Try this in a copy of your workbook.

Sub Apply_CF()
  With Intersect(Range("F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X"), Rows("3:70"))
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=F3=$Z3"
    With .FormatConditions(1)
      .Font.Color = -16753384
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13561798
      .Interior.TintAndShade = 0
    End With
  End With
End Sub

 

[rdomingue]

RickXL:

Beautiful. Works like a champ. I also learned a new method in the process.

Thanks for your time.

Randy

 

[rdomingue]

Peter:

After trying RickXL's suggestion I stumbled across yours. This works great. Both methods are quicker than the loop I was running through. I'm an old process control ladder logic programmer and tend to run to the "do loop" too quick sometimes. Thanks for reaching out to help and teach me something new.

Kind Regards,

Randy

 

[Peter_SSs]

Peter:

After trying RickXL's suggestion I stumbled across yours. This works great. Both methods are quicker than the loop I was running through. I'm an old process control ladder logic programmer and tend to run to the "do loop" too quick sometimes. Thanks for reaching out to help and teach me something new.

Kind Regards,

Randy

You are welcome.


BTW, with reference to this comment:

The formula is a bit tricky. If you were to type it into the conditional formatting dialog you would put: =$Z3
So I concatenate "=" with the address. The (False, True) controls where the "$" goes.

Since you are already putting the "Z3" into the code, you might as well put the $ in in directly too without the trouble of using the Range( ).Address( , ) structure.

<del>.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=" & Range("Z3").Address(False, True)</del>
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$Z3"

 

[RickXL]

Since you are already putting the "Z3" into the code, you might as well put the $ in in directly too without the trouble of using the Range( ).Address( , ) structure.

Thank you, Peter. I did think that I had tried that and found that it did not work. It works today, though!

Perhaps I am not as awake as I think I am at 4:00am.

Regards,

 

[Peter_SSs]

Perhaps I am not as awake as I think I am at 4:00am. ,

Definitely past bedtime !!