conditional formatting - break on Chg in Seq (Col A)
 

In Range A1:A10 I have the values:
1
2
3
4
1 << break in seq (is less or the same as previous row)
2
3
1 << break in seq (is less or the same as previous row)
1 << break in seq (is less or the same as previous row)
2

Columns B:L down to Row 10 have various other data

I would like a Macro that will perform conditional Formatting
in a toggle-fashion (green/blue) breaking on a sequence change in Col A;
Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue

Can someone suggest a start?

conditional formatting - break on Chg in Seq (Col A)
 

Hi Jim,

Try the below code.
Sub CondFormat()
'This macro assumes that the change condition is space in column A
'You can change it to your condition in the IF statement below
Dim i As Integer
Dim j As Integer
Dim mLastRow As Integer
j = 1
'Find the last row which has data in column A
mLastRow = Range("A65000").End(xlUp).Row
'Start from row 1 until last row

For i = 1 To mLastRow + 1
If Range("A" & i).Value = "" Then 'If the change in pattern found
Range("B" & j & ":L" & i - 1).Select
Selection.Interior.ColorIndex = 6
j = i + 1
End If
Next
'Move the selction to cell A1
Range("A1").Select

End Sub

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Jim May" wrote:

In Range A1:A10 I have the values:
1
2
3
4
1 << break in seq (is less or the same as previous row)
2
3
1 << break in seq (is less or the same as previous row)
1 << break in seq (is less or the same as previous row)
2

Columns B:L down to Row 10 have various other data

I would like a Macro that will perform conditional Formatting
in a toggle-fashion (green/blue) breaking on a sequence change in Col A;
Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue

Can someone suggest a start?

conditional formatting - break on Chg in Seq (Col A)
 

Format all the cells in green, then use conditional formatting to override
that setting
Select all the data cells in the second row, then use Format-Conditional
formatting and choose

Formula is

and use this formula

=$a2<=$a1

Now click on the format button, go to the patterns tab and choose the blue
color you want.

OK your way back to the worksheet, then paint the format from this row down
to all the other rows


"Jim May" wrote:

In Range A1:A10 I have the values:
1
2
3
4
1 << break in seq (is less or the same as previous row)
2
3
1 << break in seq (is less or the same as previous row)
1 << break in seq (is less or the same as previous row)
2

Columns B:L down to Row 10 have various other data

I would like a Macro that will perform conditional Formatting
in a toggle-fashion (green/blue) breaking on a sequence change in Col A;
Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue

Can someone suggest a start?

conditional formatting - break on Chg in Seq (Col A)
 

Pranav:
After running macro - My sheet ends up with the Range(B1:L10) fully yellow
in color
with Column A unformatted -- not the desired results.
Thanks,
Jim

"Pranav Vaidya" wrote:

Hi Jim,

Try the below code.
Sub CondFormat()
'This macro assumes that the change condition is space in column A
'You can change it to your condition in the IF statement below
Dim i As Integer
Dim j As Integer
Dim mLastRow As Integer
j = 1
'Find the last row which has data in column A
mLastRow = Range("A65000").End(xlUp).Row
'Start from row 1 until last row

For i = 1 To mLastRow + 1
If Range("A" & i).Value = "" Then 'If the change in pattern found
Range("B" & j & ":L" & i - 1).Select
Selection.Interior.ColorIndex = 6
j = i + 1
End If
Next
'Move the selction to cell A1
Range("A1").Select

End Sub

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Jim May" wrote:

In Range A1:A10 I have the values:
1
2
3
4
1 << break in seq (is less or the same as previous row)
2
3
1 << break in seq (is less or the same as previous row)
1 << break in seq (is less or the same as previous row)
2

Columns B:L down to Row 10 have various other data

I would like a Macro that will perform conditional Formatting
in a toggle-fashion (green/blue) breaking on a sequence change in Col A;
Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue

Can someone suggest a start?

conditional formatting - break on Chg in Seq (Col A)
 

Duke:
Thanks, but this only changes the First-record of the break-in-sequence.
I was wanting to continue with the same blue color for the remaining rows in
the new sequence - note Rows 5:7 = Blue -- not just Row 5 alone.

Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue

Thanks,

Jim


"Duke Carey" wrote:

Format all the cells in green, then use conditional formatting to override
that setting
Select all the data cells in the second row, then use Format-Conditional
formatting and choose

Formula is

and use this formula

=$a2<=$a1

Now click on the format button, go to the patterns tab and choose the blue
color you want.

OK your way back to the worksheet, then paint the format from this row down
to all the other rows


"Jim May" wrote:

In Range A1:A10 I have the values:
1
2
3
4
1 << break in seq (is less or the same as previous row)
2
3
1 << break in seq (is less or the same as previous row)
1 << break in seq (is less or the same as previous row)
2

Columns B:L down to Row 10 have various other data

I would like a Macro that will perform conditional Formatting
in a toggle-fashion (green/blue) breaking on a sequence change in Col A;
Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue

Can someone suggest a start?