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

rdomingue

New Member
Joined
Mar 18, 2016
Messages
8
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,327
Hi and welcome to the MrExcel Message Board.

Try this:
Code:
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,
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Randy

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

Rich (BB code):
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

New Member
Joined
Mar 18, 2016
Messages
8
RickXL:

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

Thanks for your time.

Randy
 

rdomingue

New Member
Joined
Mar 18, 2016
Messages
8

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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. :)

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

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,327
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. :confused:

Regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,990
Messages
5,599,226
Members
414,297
Latest member
dalkarl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top