• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Alternate a Row Color in Conditional Formatting?

P

patougaffou

New Member
Hi everybody,


I like to write a conditional formatting rule that will alternate the row color each time that the cell value changes.


for example in column A1


A1

1 ----> line will be red

1 ----> line will be red

1 ----> line will be red

2 ----> line will be blue

3 ----> line will be red

4 ----> line will be blue

4 ----> line will be blue

4 ----> line will be blue

5 ----> line will be red


Thank a lot for helping.
 
In Excel 2010 you can create your own rule in 'Conditional Formatting--New rule'. There are a whole slew of choices, perhaps the one you want would be--'Format only cells that contain', from there you could choose a fill colour to format a cell that contains your set number, or you could choose greater than or less than. As I said there are many so play around there is a lot of power hidden there
 
Patougaffou


Firstly, Welcome to the Chandoo_Org Forums


Have a look at: http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors
 
Hi,Patougaffou!


Once you read the three green sticky topics at this forums main page, you can try applying conditional format with this formulas:

=MOD($A1,2)=0 and set color to blue and apply to proper range

=MOD($A1,2)=1 and set color to red and apply to same range


Regards!
 
Sorry but none of these issues works and I forgot to mention in my title post that it is alternating depending a cell value of the column AA.


Anyway I couldn't test the excelhero's formula.


Thank if someone could analyze this problem helping me.
 
Patougaffou


That is exactly what the post I gave above does ?

http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors


Can you please upload a copy of your data
 
Thanks Hui,


it may sound stupid but I couldn't find the way to upload my sample file.

Anyway I have already checked your link and "=MOD($A1,2)=0" & "=MOD($A1,2)=1" may give the impression that is working.

But what if two odd numbers or even numbers are in sequence.


Thanks
 
Have you read the three sticky posts on the forum home page

Specifically http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi, patougaffou!


As I read in the comments in your file, you not only want to alternate colors for even/odd numbers in column A, you seem to want to alternate them whenever two or more odd or two or more even numbers appear sequentially.

Why don't you please clarify this point and write down the exact requirement? Sans ecrire un cahier de charges :)

Regards!
 
Hi ,


I got the solution from here :


http://www.cpearson.com/excel/ContentBanding.aspx


1. Choose any helper column , say column K.


2. In K1 , enter the value TRUE.


3. In K2 , enter the following formula : =IF(A2=A1,K1,NOT(K1))


4. Copy this formula downwards as far as your data extends.


5. In CF , select the first row , and enter the following formula : =$K1 , and choose the first colour.


6. With the same row selected , enter the second CF rule as : =NOT($K1) , and choose the second colour.


Apply.


Now , using the format painter , copy this CF to all the remaining rows.


Instead of entering TRUE in K1 , if you enter FALSE , the colour selection will be reversed.


Narayan
 
here is another sample workbook.


http://speedy.sh/SGKFj/Conditional-Formating-Case.xlsx


What I want is alternate the row color each time the values in column A changes.

I said that "=MOD($A1,2)=0" & "=MOD($A1,2)=1" works fine if it is a normal sequence but in case there is not a normal sequence I mean not "odd-even-odd-even-odd even etc..."

it doesn't work


Thanks
 
Thanks a lot this is exactly what i need it, and Chandoo gave already the solution but as my excel version is in french I couldn't make it work. Now that I download the file it convert it in french.


Thanks for everybody that gave help again.
 
Hi, patougaffou!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top