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?

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?

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?

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?

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?