ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Loops always trip me up - and here's an example (https://www.excelbanter.com/excel-programming/396945-loops-always-trip-me-up-heres-example.html)

Jim May
Loops always trip me up - and here's an example
 
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

Gleam
Loops always trip me up - and here's an example
 
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

"Jim May" wrote:

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

Jim May
Loops always trip me up - and here's an example
 
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

"Gleam" wrote:

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

"Jim May" wrote:

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

Jim May
Loops always trip me up - and here's an example
 
One last Question..
How would I limit the Conditional formatting to Only Columns A:G versus
the entire row from columns A:IV?


"Gleam" wrote:

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

"Jim May" wrote:

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

Jim May
Loops always trip me up - and here's an example
 
Never mind -- I changed all instances of ="TRUE" to:

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

Much obliged.

Jim


"Jim May" wrote:

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

"Gleam" wrote:

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

"Jim May" wrote:

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

Gleam
Loops always trip me up - and here's an example
 
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

"Jim May" wrote:

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


"Gleam" wrote:

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

"Jim May" wrote:

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


All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com