Apply Colurs to alternate ranges

[LGMan]

Hi,
I'd like to apply color formatting to alternate blocks of data (based on a change to Column A values. An example of the data is
Area Sales ID Sales Sales Rank
West ASC 785 4
West SHC 984 1
West JFP 824 3
Mids SLH 683 6
Mids EMS 716 5
South RGF 598 7
South LMG 853 2
The number of rows per Area is not fixed so a simple A23 etc is no good and the values of Col.A is not fixed to the simple example.
Any help greatly received

 

[mintjulep]

Why not simply use conditional formatting?

 

[SkipVought]

use this in the Conditional Formatting FORMULA...
[tt]
=MOD(row(),2)=0
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks for the quick replies, however, I probably didn't make it clear in my example,

What I'd like to do is color all of West and South data and so on so that every other 'block' of data is colored.
My real data has 32 blocks of data which varies in size each week, in reality week 1 A226 can be 'West data' but week2 it may be A229.
Hope this make sense

Thanks

 

[mintjulep]

Do you need 32 different colors?

 

[LGMan]

No, I need the range of data to be either yellow or white, so odd numbered blocks are yellow and even numbered blocks are white.

 

[SkipVought]

You can STILL do this via Conditional Formatting without any VBA code.
Define your table in Data > List

Define your Conditional Formatting.

VOLA!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

ok, I get what you say about no code required, however I have a number of routines to perform on the raw data to get to the finished presentation report. I was just looking to accomplish all the work within one procedure.

 

[SkipVought]

what is it that you want to accomplish?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

ok, going back to my example, I'd just like to color a range of data so that all the data for first range of data - West has yellow interior, the second range of data - Mids has white interior, the third range - South has Yellow Interior and so on. It's a change to the Area value which needs to make the effect to the interior color. My real data is way larger and can varies by + or - 10% each week, so that each distinct area could occupy anything from 18 to 22 rows. As this color banding by Area is just part of the requirement to turn un-formatted data into a presentation style 'report' I was looking to incorporate it into a single 'report formatting' procedure

 

[SkipVought]

Well that's different than alternate rows.

Use Column E as a helper column...
[tt]
E2: =IF(A2=A1,E1,IF(ISNUMBER(E1),E1,0)+1)
[/tt]
Now reference this value in your CF
[tt]
=MOD($e2,2)=0
[/tt]
But you STILL have not described, "number of routines to perform on the raw data"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks Skip, it's a fair cop, it isn't a row as you say. it's only 4 columns wide in my example (and 14 in my real file). I haven't bothered listing all the formatting and function stuff I have to do as this all fits into easy steps just right for VBA.

 

[SkipVought]

4 or 14; makes no difference. same principle.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}