Alright folks I have been breaking my head with this problem and here
you go. I have a sheet which has about 250000 records. There is one
column which will be the base of this question.

Home Country
Albania
<blank>
<blank>
Australia
<blank>
<blank>
<blank>
<blank>
<blank>
<blank>
Austria
<blank>
<blank>
<blank>
<blank>
<blank>

My Question is I have to format my entire sheet using this column,
where I need alternate colors for each country and the blank cells
below it.I.E., From Albania till Australia it should be in one color
and from Australia to Austria it should be in another color. I have
tried Conditional Formatting formulas and tried playing around with my
limited knowledge over VBA Scripts.

I am using Excel 2007 and MS Vista if that helps....Any takers out
there for this problem ?

Vinod Chandramouli

On Sun, 7 Dec 2008 03:54:06 -0800 (PST), Vinod Chandramouli
<(E-Mail Removed)> wrote:

>Alright folks I have been breaking my head with this problem and here
>you go. I have a sheet which has about 250000 records. There is one
>column which will be the base of this question.
>
>Home Country
>Albania
><blank>
><blank>
>Australia
><blank>
><blank>
><blank>
><blank>
><blank>
><blank>
>Austria
><blank>
><blank>
><blank>
><blank>
><blank>
>
> My Question is I have to format my entire sheet using this column,
>where I need alternate colors for each country and the blank cells
>below it.I.E., From Albania till Australia it should be in one color
>and from Australia to Austria it should be in another color. I have
>tried Conditional Formatting formulas and tried playing around with my
>limited knowledge over VBA Scripts.
>
>I am using Excel 2007 and MS Vista if that helps....Any takers out
>there for this problem ?
>
>Vinod Chandramouli

1. Select the column to be formatted
This should result in the cell in Row 1 being the active cell.
2. Conditional Formatting
New Rule
Use a formula to determine ...

(assuming Column A)
3 Format values where this formula is true:
=MOD(COUNTA($A$1:A1),2)=1
(Format to taste)
<OK>

4 New Rule
Format values where this formula is true:
=MOD(COUNTA($A$1:A1),2)=0
(Format to taste)
<OK>

--ron

Ron,
Thanks for your response. I think we might be on the wrong page.
Your requirement colors alternate tab which is the concept but here is
what i want.

Home Country
Albania - Blue
<blank> - Blue
<blank> - Blue
Australia - Green
<blank> - Green
<blank> - Green
<blank> - Green
<blank> - Green
<blank> - Green
<blank> - Green
Austria - Blue
<blank> - Blue
<blank> - Blue

I hope this helps.

-- vinod chandramouli

may be this ?
select the first cell which has value

Sub color_it()
Do Until ActiveCell.Value = ""
Range(Selection, Selection.End(xlDown)).Select
Selection.Interior.ColorIndex = 5
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Interior.ColorIndex = 4
Selection.End(xlDown).Select
Loop
End Sub


On Dec 7, 6:31*pm, Vinod Chandramouli <chandramouli.vi...@gmail.com>
wrote:
> Ron,
> * Thanks for your response. I think we might be on the wrong page.
> Your requirement colors alternate tab which is the concept but here is
> what i want.
>
> Home Country
> Albania * * * * * * *- Blue
> <blank> * * * * * * - Blue
> <blank> * * * * * * - Blue
> Australia * * * * * *- Green
> <blank> * * * * * *- Green
> <blank> * * * * * *- Green
> <blank> * * * * * *- Green
> <blank> * * * * * *- Green
> <blank> * * * * * *- Green
> <blank> * * * * * *- Green
> Austria * * * * * * *- Blue
> <blank> * * * * * *- Blue
> <blank> * * * * * *- Blue
>
> I hope this helps.
>
> -- vinod chandramouli

On Sun, 7 Dec 2008 05:31:52 -0800 (PST), Vinod Chandramouli
<(E-Mail Removed)> wrote:

>Ron,
> Thanks for your response. I think we might be on the wrong page.
>Your requirement colors alternate tab which is the concept but here is
>what i want.
>
>Home Country
>Albania - Blue
><blank> - Blue
><blank> - Blue
>Australia - Green
><blank> - Green
><blank> - Green
><blank> - Green
><blank> - Green
><blank> - Green
><blank> - Green
>Austria - Blue
><blank> - Blue
><blank> - Blue
>
>I hope this helps.
>
>-- vinod chandramouli

I hope that when you wrote "tabs" you meant "rows" as the routine I gave you
will have no effect on any tabs.

However, if it is coloring alternate rows, then either your blank cells are not
blank, or you are not following my instructions properly.

Let's see if we can figure this out.

Please select the first cell in your column.
Then select Conditional Formatting
Manage rules

You should see two rules

Copy the contents of the
Applies to:
box and paste it in your reply.

Then select that rule again;
select Edit Rule
copy the Rule Description from the box and paste it into your reply.


--ron

Ron,
Here you go...

Applies To
1) =$B:$B
2) =$B:$B

Rule Description
1) =MOD(SUBTOTAL(3,$B$10:$B11),2)
2) =MOD(SUBTOTAL(3,$B$10:$B11),2)

And I stand corrected I meant rows not columns.

On Sun, 7 Dec 2008 08:12:25 -0800 (PST), Vinod Chandramouli
<(E-Mail Removed)> wrote:

>Ron,
> Here you go...
>
>Applies To
>1) =$B:$B
>2) =$B:$B
>
>Rule Description
>1) =MOD(SUBTOTAL(3,$B$10:$B11),2)
>2) =MOD(SUBTOTAL(3,$B$10:$B11),2)
>
>And I stand corrected I meant rows not columns.


There are a number of differences between the formula I recommended and that
which you are using.

=MOD(COUNTA($A$1:A1),2)=1
=MOD(COUNTA($A$1:A1),2)=0

1. It's probably OK to substitute the SUBTOTAL(3,...) if you don't want to
include rows that are suppressed by the Filter.

2. It is NOT ok to omit the equality (at least the =0 should be there) as 1
and 0 may not necessarily being evaluated as TRUE or FALSE.
enter =1=TRUE on your worksheet and you will see this evaluates to
FALSE; as does =0=FALSE.

3. Your modified formulas,should read:

=MOD(SUBTOTAL(3,$B$1:$B1),2)=1
=MOD(SUBTOTAL(3,$B$1:$B1),2)=0

--ron