LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 11:51 PM.

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"