Excel Conditional Formatting 1

[Joeclueless]

Hi,

I have 3 columns of data.

Zipcode Zipcode % Local District*

There are 444 rows in the worksheet.
starting at row 2 and thru row 444,I have 2 conditional formatting formulas that are working.

1) =$C2>0 Formatting = Green Bold Text
2) =$C2=0 Formatting = Red Bold Text

These work fine....

I want a third conditional formatting, or possibly incorporate this third condition in the first 2.

The idea is to have the rows with like values in column A have grey background color, then the next group of rows with like values in column A have white background color and altrenate as such through the rest of the data.

I have tried this for formula 3:

3) =ROUND(MOD(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1,$A$1:$A1)+($A$1:$A$1=""))),2),0)

But this dosent work as number 3

It does as number 1, but not quite right... It excludes the first instance in each groupping....

Any Ideas??





Thanks again!

Joe

 

[KenWright]

CFs are not cumulative. In the order they appear ie 1,2,3 as soon as it hits one that is true then the CF stops looking and won't consider the others.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Joeclueless]

Thanks Ken!

Have a star for enlightening me of the non-cumulative fact!

However, I still am having trouble with this task...



Thanks again!

Joe

 

[bluedragon2]

=AND(COUNTIF($A$1:$A$444,A1)>1,A1>0) will activate if you have a duplicate and the value is greater then 0.

You should be able to figure the rest.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Joeclueless]

Actually Blue,

I am having a bit of trouble with that...

Am I understanding the code?

=AND(COUNTIF($A$1:$A$444,A1)>1,A1>0)

I think that in english, your code says:

"=AND(COUNTIF" means apply the format if

"($A$1:$A$444,A1)>1" means column A Row 1 thru column A row 444 more than one instance

",A1>0)" means when the cell in column A and row 1 is greater than 0


Using this code as posted produced these results

A B C
_________________
-90 93.6% 0
-90 6.4% 2
90001 19.2% 6
90001* 80.6% 7
90001* 0.2% 5
90002* 0.3% 6
90002* 99.8% 7
90003* 100.0% 7
90004 26.9% 3
90004* 73.1% 4

The astrist indicates the cell is effected by the conditional formatting.

Preferably, I would like the results to be as such:

A B C
_________________
-90** 93.6%** 0** ** meaning C=0 so all text Bold Red (Dup in column A makes grey background color)
-90* 6.4%* 2* * Dup in column A makes grey background color
90001* 19.2%* 6*
90001* 80.6%* 7* * Dup in column A makes altrenate background color (white)
90001* 0.2%* 5*
90002* 0.3%* 6* * Dup in column A makes grey background color
90002* 99.8%* 7*
90003 100.0% 7 no dup in A and greater than 0 in C make no format
90004* 26.9%* 3*
90004* 73.1%* 4* * Dup in column A makes grey background color


Now, this may be too confusing...

So it comes down to:

1)if its a group of like values in column A, it gets the background grey-
2)the next group of like values in column A, stays with white background
3)anytime the value in column C is 0, the text is red and it keeps the correct grouped background color..

Clear as mud right?



Thanks again!

Joe

 

[bluedragon2]

From my understanding of what you want, you can not do with conditional formating. You will need VBA code.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Joeclueless]

Oh well.........




Thanks again!

Joe