Conditional format in every 6th row

[snuggles57]

I need to paste Format conditional formatting to every 6th row with the starting range ("I22:AM22")
This has to continue until the equivalent range (every 6th row) in cell "A22" is blank
Thanks for any help!


Range("I22:AM22").Select
Range("I22").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65280
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16776960
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With

 

[Jeffrey Mahoney]

How about something like this. You don't have to repeat the conditional formatting if you use a formula. In this case ht MOD function gets the remainder from dividing the Row with 6, if the result is zero (multiple of 6) then format the cell with lines on all four sides. I used the range I22:I100, you can specify otherwise.

Sub Macro2()


    Cells.FormatConditions.Delete
    Range("I22:I100").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(I22),6)=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Jeffrey Mahoney]

If you want the formatting to start at I22 and then format every sixth cell from there then your formula would be:

=MOD(ROW(I22)-ROW($I$22),6)=0

 

[snuggles57]

[TABLE="width: 836"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Thanks for your reply Jeff[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]I am a novice with VBA and have tried the following.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sub Macro2()[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cells.FormatConditions.Delete[/TD]
[TD] (Removed this as I want to retain the conditional format but wanted to add additional conditions)[/TD]
[/TR]
[TR]
[TD] Range("I22:AM22").Select [/TD]
[TD]This is the range for the first Row[/TD]
[/TR]
[TR]
[TD] Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/TD]
[TD]No change[/TD]
[/TR]
[TR]
[TD] "=MOD(ROW(I22),6)=0"[/TD]
[TD]No change[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Then added the conditional format I needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]This works well on the first row I22:AM22 but does not continue to the next group of rows which would be I28:AM28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Also the above adds an extra conditional format with this formula =$I$22:$AM$22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]am I doing something wrong?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Mahoney]

You want to select all your rows. This macro does not need to repeat itself.

Range("I22:AM22").Select Needs to be Range("I22:AM1000").Select ( or to whatever row you need to extend the formatting).

The formula "=MOD(ROW(I22),6)=0" determines if the row falls within the criteria

Jeff

 

[snuggles57]

Hello again Jeff
I don't seem to be able to make this work or maybe I didn't explain very well but I don't want to change the colour of every sixth row I want to apply the conditional formatting that changes each cell depending on it's value for example if the cell has the number 6 then change the colour of that cell to bright green if number 3 then change to bright blue 2 change to bright Yellow.

I only want to apply this formatting for cells I22:AM22 then every sixth row.

Then I would apply similar formatting based on different criteria based on text eg A would be orange B another colour and continue until I reach the letter O this would be applied to every sixth row using Range I23:AM23

Can this be done? Or am I dreaming!

 

[Jeffrey Mahoney]

Well, it can be done. For each Character/Color combination is a new conditional format. If you're planning on having a different Character/Color combination across columns I22 to AM22 (31 columns), and then the same on row 23, 24, 25, 26, and 27; well that sounds like a lot of work. And it's going to slow down the workbook.

If it's only a few on each line, then maybe that's more manageable. You would have to combine the formula I gave you above with the Character you want to check. I wouldn't bother trying to use VBA for this, I would just bash through it manually. Mainly because the formula does the row checking and you don't need a looping macro to create a conditional format on every new line.

When you create the conditional formatting, you want to affected range (highlighted range) be I22:AM100 (or to whatever last row you need)

For row 22
=AND(MOD(ROW(I22)-ROW($I$22),6)=0,I22="A") (orange)
or
=AND(MOD(ROW(I22)-ROW($I$22),6)=0,I22="B") (green)

For Row 23
=AND(MOD(ROW(I23)-ROW($I$23),6)=0,I23="O") (blue)
or
=AND(MOD(ROW(I23)-ROW($I$23),6)=0,I23="Y") (yellow)

In each of those formulas, the anchor cell is the top left cell you have in your affected range. Whenever you create a formula based conditional formatting, always reference the top left cell. Excel does the rest to apply the formatting to the rest of the cells. If you use $ to anchor your cells you can lock the column or row so it only applies to that specific column or row.

The formulas test both the relative row and the cell contents. Just try one and see how it works

Jeff