J
Jim May
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
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