Alternate shading based on series of numbers in 3 columns

Hi,

XL 2003.

I have a range that spreads over Cols A:H, with a dynamic number of
rows, starting at Row 15.

The range is sorted into numeric order based on Cols F - H.

I wish to employ alternate shading (Cols A - H) starting at Row 15,
based on each series identified in Cols F - H (these are dynamic), eg
(Note that I have only exampled Cols F - H).

Col F Col G Col H
R15 1 0 0 (No shade)
R16 1 0 0 (No shade)
R17 1 0 0 (No shade)
R18 1 1 0 (Shading)
R19 1 1 1 (No shade)
R20 1 1 1 (No shade)
R21 2 0 3 (Shading)
R22 2 0 3 (Shading)
R23 4 0 0 (No shade)
etc
etc

I would like to automate this task, that is currently being done
manually.

Many Thanks

Bob
Maitland Australia

Hi Bo,

It's not obvious what condition you want to shade your rows. The only thing
that appears in common in your example is if 2 of 3 values <> 0

If that guess is correct try conditional formatting. Select cell A15 and in
the CF dialog
select the "Formula is" setting

=(($F15<>0)+($G15<>0)+($H15<>0))=2

Paste special formats in over the range A15:Hx where x is the last row that
might need shading.

Regards,
Peter T

"Ozbobeee" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> XL 2003.
>
> I have a range that spreads over Cols A:H, with a dynamic number of
> rows, starting at Row 15.
>
> The range is sorted into numeric order based on Cols F - H.
>
> I wish to employ alternate shading (Cols A - H) starting at Row 15,
> based on each series identified in Cols F - H (these are dynamic), eg
> (Note that I have only exampled Cols F - H).
>
> Col F Col G Col H
> R15 1 0 0 (No shade)
> R16 1 0 0 (No shade)
> R17 1 0 0 (No shade)
> R18 1 1 0 (Shading)
> R19 1 1 1 (No shade)
> R20 1 1 1 (No shade)
> R21 2 0 3 (Shading)
> R22 2 0 3 (Shading)
> R23 4 0 0 (No shade)
> etc
> etc
>
> I would like to automate this task, that is currently being done
> manually.
>
> Many Thanks
>
> Bob
> Maitland Australia

Tks for the reply, Peter.

To clarify, each row, starting at 15, is grouped together, based on
the values in Cols F - H eg.
All entries with 1,0,0 are grouped together, all with 1,1,0 are
grouped together etc.

The combination 0,0,0 will never show, nor will any negative values.


Rather than shade by alternate rows, I wish to have alternate shading
Cols (A - H) based on a change in sequence (groups) of the values in
Cols F - H.

The worksheet is used on a fortnightly basis at which time the
individual values in Cols F - H change. Thus the need for code.

Any assistance appreciated.

Cheers

Bob


On Sun, 18 Sep 2005 15:04:20 +0100, "Peter T" <peter_t@discussions>
wrote:

>Hi Bo,
>
>It's not obvious what condition you want to shade your rows. The only thing
>that appears in common in your example is if 2 of 3 values <> 0
>
>If that guess is correct try conditional formatting. Select cell A15 and in
>the CF dialog
>select the "Formula is" setting
>
>=(($F15<>0)+($G15<>0)+($H15<>0))=2
>
>Paste special formats in over the range A15:Hx where x is the last row that
>might need shading.
>
>Regards,
>Peter T
>
>"Ozbobeee" <[email protected]> wrote in message
>news:[email protected]...
>> Hi,
>>
>> XL 2003.
>>
>> I have a range that spreads over Cols A:H, with a dynamic number of
>> rows, starting at Row 15.
>>
>> The range is sorted into numeric order based on Cols F - H.
>>
>> I wish to employ alternate shading (Cols A - H) starting at Row 15,
>> based on each series identified in Cols F - H (these are dynamic), eg
>> (Note that I have only exampled Cols F - H).
>>
>> Col F Col G Col H
>> R15 1 0 0 (No shade)
>> R16 1 0 0 (No shade)
>> R17 1 0 0 (No shade)
>> R18 1 1 0 (Shading)
>> R19 1 1 1 (No shade)
>> R20 1 1 1 (No shade)
>> R21 2 0 3 (Shading)
>> R22 2 0 3 (Shading)
>> R23 4 0 0 (No shade)
>> etc
>> etc
>>
>> I would like to automate this task, that is currently being done
>> manually.
>>
>> Many Thanks
>>
>> Bob
>> Maitland Australia
>

Hi Bob,

I follow now that you want alternate vertical shading in columns, but I
don't follow what criteria from values in Cols F-H defines whether or not to
apply banded shading. Also will all columns have the same shading or does
each row of alternate cells have its own banded shading.

Regards,
Peter T


"Ozbobeee" <[email protected]> wrote in message
news:[email protected]...
> Tks for the reply, Peter.
>
> To clarify, each row, starting at 15, is grouped together, based on
> the values in Cols F - H eg.
> All entries with 1,0,0 are grouped together, all with 1,1,0 are
> grouped together etc.
>
> The combination 0,0,0 will never show, nor will any negative values.
>
>
> Rather than shade by alternate rows, I wish to have alternate shading
> Cols (A - H) based on a change in sequence (groups) of the values in
> Cols F - H.
>
> The worksheet is used on a fortnightly basis at which time the
> individual values in Cols F - H change. Thus the need for code.
>
> Any assistance appreciated.
>
> Cheers
>
> Bob
>
>
> On Sun, 18 Sep 2005 15:04:20 +0100, "Peter T" <peter_t@discussions>
> wrote:
>
> >Hi Bo,
> >
> >It's not obvious what condition you want to shade your rows. The only
thing
> >that appears in common in your example is if 2 of 3 values <> 0
> >
> >If that guess is correct try conditional formatting. Select cell A15 and
in
> >the CF dialog
> >select the "Formula is" setting
> >
> >=(($F15<>0)+($G15<>0)+($H15<>0))=2
> >
> >Paste special formats in over the range A15:Hx where x is the last row
that
> >might need shading.
> >
> >Regards,
> >Peter T
> >
> >"Ozbobeee" <[email protected]> wrote in message
> >news:[email protected]...
> >> Hi,
> >>
> >> XL 2003.
> >>
> >> I have a range that spreads over Cols A:H, with a dynamic number of
> >> rows, starting at Row 15.
> >>
> >> The range is sorted into numeric order based on Cols F - H.
> >>
> >> I wish to employ alternate shading (Cols A - H) starting at Row 15,
> >> based on each series identified in Cols F - H (these are dynamic), eg
> >> (Note that I have only exampled Cols F - H).
> >>
> >> Col F Col G Col H
> >> R15 1 0 0 (No shade)
> >> R16 1 0 0 (No shade)
> >> R17 1 0 0 (No shade)
> >> R18 1 1 0 (Shading)
> >> R19 1 1 1 (No shade)
> >> R20 1 1 1 (No shade)
> >> R21 2 0 3 (Shading)
> >> R22 2 0 3 (Shading)
> >> R23 4 0 0 (No shade)
> >> etc
> >> etc
> >>
> >> I would like to automate this task, that is currently being done
> >> manually.
> >>
> >> Many Thanks
> >>
> >> Bob
> >> Maitland Australia
> >
>

Hi Peter,

I really haven't explained the situation that well, have I? :-)

The dynamic range, starting at A15 was sorted numerically, based on
the corresponding values in Cols F, G, and H. Thus, in the example
Rows 15:17 were grouped because they all had the values 1, 0, 0 in the
corresponding cells in Cols F, G, and H.

The next grouping only had one row to it - Row 18 with values of 1, 1,
0..

The third grouping had two rows, 19 and 20 each with the values of 1,
1, 1.

Initially I wanted each alternate grouping of rows to be shaded.

I've had a re-think and decided that a better option may be to simply
have code that will insert a blank row between each of the groupings
and then shade these blank rows.

In essence I was simply trying to make the sheet easier to read for
data entry purposes, which the blank row scenario accomodates.

I really appreciate you taking the time to respond.

Thanks again.

Cheers

Bob




On Mon, 19 Sep 2005 09:31:13 +0100, "Peter T" <peter_t@discussions>
wrote:

>Hi Bob,
>
>I follow now that you want alternate vertical shading in columns, but I
>don't follow what criteria from values in Cols F-H defines whether or not to
>apply banded shading. Also will all columns have the same shading or does
>each row of alternate cells have its own banded shading.
>
>Regards,
>Peter T
>
>
>"Ozbobeee" <[email protected]> wrote in message
>news:[email protected]...
>> Tks for the reply, Peter.
>>
>> To clarify, each row, starting at 15, is grouped together, based on
>> the values in Cols F - H eg.
>> All entries with 1,0,0 are grouped together, all with 1,1,0 are
>> grouped together etc.
>>
>> The combination 0,0,0 will never show, nor will any negative values.
>>
>>
>> Rather than shade by alternate rows, I wish to have alternate shading
>> Cols (A - H) based on a change in sequence (groups) of the values in
>> Cols F - H.
>>
>> The worksheet is used on a fortnightly basis at which time the
>> individual values in Cols F - H change. Thus the need for code.
>>
>> Any assistance appreciated.
>>
>> Cheers
>>
>> Bob
>>
>>
>> On Sun, 18 Sep 2005 15:04:20 +0100, "Peter T" <peter_t@discussions>
>> wrote:
>>
>> >Hi Bo,
>> >
>> >It's not obvious what condition you want to shade your rows. The only
>thing
>> >that appears in common in your example is if 2 of 3 values <> 0
>> >
>> >If that guess is correct try conditional formatting. Select cell A15 and
>in
>> >the CF dialog
>> >select the "Formula is" setting
>> >
>> >=(($F15<>0)+($G15<>0)+($H15<>0))=2
>> >
>> >Paste special formats in over the range A15:Hx where x is the last row
>that
>> >might need shading.
>> >
>> >Regards,
>> >Peter T
>> >
>> >"Ozbobeee" <[email protected]> wrote in message
>> >news:[email protected]...
>> >> Hi,
>> >>
>> >> XL 2003.
>> >>
>> >> I have a range that spreads over Cols A:H, with a dynamic number of
>> >> rows, starting at Row 15.
>> >>
>> >> The range is sorted into numeric order based on Cols F - H.
>> >>
>> >> I wish to employ alternate shading (Cols A - H) starting at Row 15,
>> >> based on each series identified in Cols F - H (these are dynamic), eg
>> >> (Note that I have only exampled Cols F - H).
>> >>
>> >> Col F Col G Col H
>> >> R15 1 0 0 (No shade)
>> >> R16 1 0 0 (No shade)
>> >> R17 1 0 0 (No shade)
>> >> R18 1 1 0 (Shading)
>> >> R19 1 1 1 (No shade)
>> >> R20 1 1 1 (No shade)
>> >> R21 2 0 3 (Shading)
>> >> R22 2 0 3 (Shading)
>> >> R23 4 0 0 (No shade)
>> >> etc
>> >> etc
>> >>
>> >> I would like to automate this task, that is currently being done
>> >> manually.
>> >>
>> >> Many Thanks
>> >>
>> >> Bob
>> >> Maitland Australia
>> >
>>
>

Hi Bob,

OK I think I get it now. As much me having been slow to understand as you
not to have explained. <g>

Hopefully following will do what you originally asked for, ie the shading

Option Explicit
Sub test()
Dim b1 As Boolean, b2 As Boolean
Dim rbRow As Long
Dim rtRow As Long
Dim i As Long
Dim v
Dim rng As Range

With Range("F15")
rtRow = .Row
rbRow = .End(xlDown).Row
v = Range(Cells(rtRow, .Column), Cells(rbRow, .Column + 2)).Value
Set rng = Range(Cells(rtRow, 1), Cells(rbRow, .Column + 2))
End With
rtRow = rtRow - 1

rng.Interior.ColorIndex = xlNone
rng.Rows(1).Interior.ColorIndex = 6

b1 = True
For i = 2 To UBound(v)
b2 = v(i, 1) = v(i - 1, 1) And _
v(i, 2) = v(i - 1, 2) And _
v(i, 3) = v(i - 1, 3)

If b1 = b2 Then
Range(Cells(rtRow + i, 1), Cells(rtRow + i, 8)).Interior.ColorIndex = 6
End If

If b2 = False Then
b1 = Not b1
End If
Next

End Sub

If you want to insert rows beteen bands instead of shading, adapt the above
but loop from the bottom, ie

For i = ubound(v) - 1 to 1 step -1
and compare v(i, 1) = v(i +1, 1) , etc
When you get to a "switch" insert a row and continue

Regards,
Peter T


"Ozbobeee" <[email protected]> wrote in message
news:[email protected]...
> Hi Peter,
>
> I really haven't explained the situation that well, have I? :-)
>
> The dynamic range, starting at A15 was sorted numerically, based on
> the corresponding values in Cols F, G, and H. Thus, in the example
> Rows 15:17 were grouped because they all had the values 1, 0, 0 in the
> corresponding cells in Cols F, G, and H.
>
> The next grouping only had one row to it - Row 18 with values of 1, 1,
> 0..
>
> The third grouping had two rows, 19 and 20 each with the values of 1,
> 1, 1.
>
> Initially I wanted each alternate grouping of rows to be shaded.
>
> I've had a re-think and decided that a better option may be to simply
> have code that will insert a blank row between each of the groupings
> and then shade these blank rows.
>
> In essence I was simply trying to make the sheet easier to read for
> data entry purposes, which the blank row scenario accomodates.
>
> I really appreciate you taking the time to respond.
>
> Thanks again.
>
> Cheers
>
> Bob
>
>
>
>
> On Mon, 19 Sep 2005 09:31:13 +0100, "Peter T" <peter_t@discussions>
> wrote:
>
> >Hi Bob,
> >
> >I follow now that you want alternate vertical shading in columns, but I
> >don't follow what criteria from values in Cols F-H defines whether or not
to
> >apply banded shading. Also will all columns have the same shading or does
> >each row of alternate cells have its own banded shading.
> >
> >Regards,
> >Peter T
> >
> >
> >"Ozbobeee" <[email protected]> wrote in message
> >news:[email protected]...
> >> Tks for the reply, Peter.
> >>
> >> To clarify, each row, starting at 15, is grouped together, based on
> >> the values in Cols F - H eg.
> >> All entries with 1,0,0 are grouped together, all with 1,1,0 are
> >> grouped together etc.
> >>
> >> The combination 0,0,0 will never show, nor will any negative values.
> >>
> >>
> >> Rather than shade by alternate rows, I wish to have alternate shading
> >> Cols (A - H) based on a change in sequence (groups) of the values in
> >> Cols F - H.
> >>
> >> The worksheet is used on a fortnightly basis at which time the
> >> individual values in Cols F - H change. Thus the need for code.
> >>
> >> Any assistance appreciated.
> >>
> >> Cheers
> >>
> >> Bob
> >>
> >>
> >> On Sun, 18 Sep 2005 15:04:20 +0100, "Peter T" <peter_t@discussions>
> >> wrote:
> >>
> >> >Hi Bo,
> >> >
> >> >It's not obvious what condition you want to shade your rows. The only
> >thing
> >> >that appears in common in your example is if 2 of 3 values <> 0
> >> >
> >> >If that guess is correct try conditional formatting. Select cell A15
and
> >in
> >> >the CF dialog
> >> >select the "Formula is" setting
> >> >
> >> >=(($F15<>0)+($G15<>0)+($H15<>0))=2
> >> >
> >> >Paste special formats in over the range A15:Hx where x is the last row
> >that
> >> >might need shading.
> >> >
> >> >Regards,
> >> >Peter T
> >> >
> >> >"Ozbobeee" <[email protected]> wrote in message
> >> >news:[email protected]...
> >> >> Hi,
> >> >>
> >> >> XL 2003.
> >> >>
> >> >> I have a range that spreads over Cols A:H, with a dynamic number of
> >> >> rows, starting at Row 15.
> >> >>
> >> >> The range is sorted into numeric order based on Cols F - H.
> >> >>
> >> >> I wish to employ alternate shading (Cols A - H) starting at Row 15,
> >> >> based on each series identified in Cols F - H (these are dynamic),
eg
> >> >> (Note that I have only exampled Cols F - H).
> >> >>
> >> >> Col F Col G Col H
> >> >> R15 1 0 0 (No shade)
> >> >> R16 1 0 0 (No shade)
> >> >> R17 1 0 0 (No shade)
> >> >> R18 1 1 0 (Shading)
> >> >> R19 1 1 1 (No shade)
> >> >> R20 1 1 1 (No shade)
> >> >> R21 2 0 3 (Shading)
> >> >> R22 2 0 3 (Shading)
> >> >> R23 4 0 0 (No shade)
> >> >> etc
> >> >> etc
> >> >>
> >> >> I would like to automate this task, that is currently being done
> >> >> manually.
> >> >>
> >> >> Many Thanks
> >> >>
> >> >> Bob
> >> >> Maitland Australia
> >> >
> >>
> >
>

Hi again Peter,

The code worked a treat.

I really do appreciate the time and effort you have put in.

We got there in the end..... <g>

Many Thanks,

Cheers

Bob

On Mon, 19 Sep 2005 17:33:37 +0100, "Peter T" <peter_t@discussions>
wrote:

>Hi Bob,
>
>OK I think I get it now. As much me having been slow to understand as you
>not to have explained. <g>
>
>Hopefully following will do what you originally asked for, ie the shading
>
>Option Explicit
>Sub test()
>Dim b1 As Boolean, b2 As Boolean
>Dim rbRow As Long
>Dim rtRow As Long
>Dim i As Long
>Dim v
>Dim rng As Range
>
> With Range("F15")
> rtRow = .Row
> rbRow = .End(xlDown).Row
>v = Range(Cells(rtRow, .Column), Cells(rbRow, .Column + 2)).Value
> Set rng = Range(Cells(rtRow, 1), Cells(rbRow, .Column + 2))
> End With
> rtRow = rtRow - 1
>
> rng.Interior.ColorIndex = xlNone
> rng.Rows(1).Interior.ColorIndex = 6
>
> b1 = True
> For i = 2 To UBound(v)
> b2 = v(i, 1) = v(i - 1, 1) And _
> v(i, 2) = v(i - 1, 2) And _
> v(i, 3) = v(i - 1, 3)
>
> If b1 = b2 Then
>Range(Cells(rtRow + i, 1), Cells(rtRow + i, 8)).Interior.ColorIndex = 6
> End If
>
> If b2 = False Then
> b1 = Not b1
> End If
> Next
>
>End Sub
>
>If you want to insert rows beteen bands instead of shading, adapt the above
>but loop from the bottom, ie
>
>For i = ubound(v) - 1 to 1 step -1
>and compare v(i, 1) = v(i +1, 1) , etc
>When you get to a "switch" insert a row and continue
>
>Regards,
>Peter T
>
>

<Big Snip>


>> >>
>> >
>>
>