Conditional Formatting

  • Thread starter Vinod Chandramouli
  • Start date
V

Vinod Chandramouli

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
V

Vinod Chandramouli

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
 
M

muddan madhu

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
V

Vinod Chandramouli

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.
 
R

Ron Rosenfeld

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.
Click to expand...


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
 
Top