Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Loops always trip me up - and here's an example
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Trip Planner template David Excel Discussion (Misc queries) 0 April 1st 09 05:27 PM
Calculation money entitled for official trip Sunny Excel Discussion (Misc queries) 2 May 31st 08 07:12 PM
looking for camping trip planner template excel Lsims Excel Discussion (Misc queries) 1 June 30th 06 08:53 AM
Do loops within Do loops Linking to specific cells in pivot table Excel Programming 4 April 14th 05 08:47 AM
Trip to Disney mickey Excel Programming 0 November 28th 04 04:40 PM


All times are GMT +1. The time now is 09:46 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement