Loops always trip me up - and here's an example

[Guest]

In my sheet1 I have in Range(A1:C10) the following data:
With each break in the sequence of Col A I would like to
change the entire-row color (Using Conditional Formatting)
as follows:

1 qwerq ert << Green
2 qwerq ert << Green
3 qwerq ert << Green
4 qwerq ert << Green
1 qwerq ert << Blue
2 qwerq ert << Blue
3 qwerq ert << Blue
1 qwerq ert << Green
1 qwerq ert << Blue
2 qwerq ert << Blue

Here's my code. You will see how confused I have
become. Can someone help me get it right!!!
TIA,

Sub ChgRowShadeWithBrkInSeq()
Dim MyRng As Range
Dim myPV As Long
Dim crow As Integer
crow = 2
Set MyRng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Range("1:1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = 4 'Set row 1 as Green
End With
For i = 2 To (MyRng.Rows.Count + 1)
Flag = 0
myPV = Range("A" & i).Offset(-1, 0)
If Range("A" & i).Value = myPV + 1 And Flag = 0 Then
With Range("" & crow & ":" & crow & "")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = 4 'Set row to Green
End With
End If
If Range("A" & i).Value = myPV + 1 And Flag = 1 Then
With Range("" & crow & ":" & crow & "")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = 33 'Set row to Blue
End With
End If
crow = crow + 1
Next i
End Sub

 

[Guest]

Please try the following:
Sub ChgRowShadeWithBrkInSeq()
Dim MyRng As Range
Dim myPV As Long
Dim crow As Integer, Colour As Integer
crow = 2
Set MyRng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Range("1:1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = 4 'Set row 1 as Green
End With
Colour = 4
For i = 2 To (MyRng.Rows.Count + 1)
myPV = Range("A" & i).Offset(-1, 0)
If Range("A" & i).Value <> myPV + 1 Then
If Colour = 4 Then
Colour = 33
Else
Colour = 4
End If
End If
With Range("" & crow & ":" & crow & "")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = Colour
End With
crow = crow + 1
Next i
End Sub

 

[Guest]

Gleam !!
That is so cool !! << thanks for wading-thru my mess. You saw first-hand
hope confused I became. What can I do **just to begin** understanding
looping as you seem to already have tackled (and well understnd)?

Again thanks,

Jim

 

[Guest]

One last Question..
How would I limit the Conditional formatting to Only Columns A:G versus
the entire row from columns A:IV?

 

[Guest]

Never mind -- I changed all instances of ="TRUE" to:

=AND(TRUE, Column()<=7)

Much obliged.

Jim

 

[Guest]

Jim

Please try the following. (I suspect that you do not need conditional
formating)

Sub ChgRowShadeWithBrkInSeq()
Dim MyRng As Range
Dim myPV As Long
Dim i1 As Integer, Colour As Integer
Set MyRng = Range("A2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
Range("A1:G1").Interior.ColorIndex = 4 'Set row 1 as Green
Colour = 4
For i1 = 2 To (MyRng.Rows.Count + 1)
myPV = Range("A" & i1).Offset(-1, 0)
If Range("A" & i1).Value < myPV + 1 Then
' Need to swap colours around
If Colour = 4 Then
' If green change to blue
Colour = 33
Else
' change to green
Colour = 4
End If
End If
Range("A" & i1 & ":G" & i1).Interior.ColorIndex = Colour
Next i1
End Sub

As to how to do it - it is like crosswords - the more you do the easier they
become. And having some answers helps to understand.
If this helps you may like to tick tick the yes this was helpful box?

Cheers