SOLVED

autofill colors based on even or odd numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2277414%22%20slang%3D%22en-US%22%3Eautofill%20colors%20based%20on%20even%20or%20odd%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277414%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20attempting%20to%20create%20a%20table%20of%20names%2C%20addresses%2C%20ph%20%23's%20and%20email%20addresses%20for%20the%20courts%20in%20Colorado.%3C%2FP%3E%3CP%3EThere%20are%2064%20counties%20and%20only%2022%20judicial%20districts.%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20show%20is%20one%20color%20for%20even%20numbered%20and%20a%20different%20color%20for%20odd%20judicial%20districts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2277414%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278770%22%20slang%3D%22en-US%22%3ERe%3A%20autofill%20colors%20based%20on%20even%20or%20odd%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278770%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029745%22%20target%3D%22_blank%22%3E%40bailteacher%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20use%20comma%20as%20decimal%20separator%3F%20If%20so%2C%20the%20formula%20should%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(%24C2%26lt%3B%26gt%3B%22%22%3BISEVEN(%24C2))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278576%22%20slang%3D%22en-US%22%3ERe%3A%20autofill%20colors%20based%20on%20even%20or%20odd%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%3C%2FP%3E%3CP%3EHowever%20when%20I%20place%20highlight%20the%20cells%20from%20C2%20down%20to%20C72%20and%20then%20selecting%20the%20Conditional%20Formatting%20%26gt%3B%20New%20Rule%20then%20select%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'%20and%20then%20enter%20the%20formula%20you%20sent%3A%3C%2FP%3E%3CP%3E%3DAND(%24C2%26lt%3B%26gt%3B%22%22%2CISEVEN(%24C2))%3C%2FP%3E%3CP%3EI%20then%20click%20Format%20and%20select%20the%20color%20%26amp%3B%20click%20OK%20twice%20(can't%20click%20the%20OK%20for%20the%20color%20twice).%3C%2FP%3E%3CP%3EAfter%20clicking%20the%20OK%20for%20the%20color%20an%20error%20msg%20comes%20up%20indicating%20EXCEL%20thinks%20I'm%20trying%20to%20create%20a%20formula%20and%20I%20need%20to%20place%20an%20apostrophe%20before%20the%20%3D%20so%20EXCEL%20doesn't%20get%20confused.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hit%20the%20OK%20button%20for%20the%20error%20msg%20and%20nothing%20happens.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20understand%20what%20I'm%20doing%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2277424%22%20slang%3D%22en-US%22%3ERe%3A%20autofill%20colors%20based%20on%20even%20or%20odd%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277424%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029745%22%20target%3D%22_blank%22%3E%40bailteacher%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20judicial%20district%20numbers%20are%20in%20column%20C%2C%20starting%20in%20C2.%3C%2FP%3E%0A%3CP%3ESelect%20row%202%20to%20however%20far%20down%20you%20want%20to%20go.%20The%20active%20cell%20in%20the%20selection%20should%20be%20in%20row%202.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%26nbsp%3B%26nbsp%3B%20%3DAND(%24C2%26lt%3B%26gt%3B%22%22%2CISEVEN(%24C2))%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20color%2C%20then%20click%20OK%20twice.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20the%20above%20steps%20with%20the%20formula%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DISODD(%24C2)%26nbsp%3B%26nbsp%3B%20and%20another%20color.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm attempting to create a table of names, addresses, ph #'s and email addresses for the courts in Colorado.

There are 64 counties and only 22 judicial districts.

What I want to show is one color for even numbered and a different color for odd judicial districts.

 

3 Replies

@bailteacher

Let's say the judicial district numbers are in column C, starting in C2.

Select row 2 to however far down you want to go. The active cell in the selection should be in row 2.

 

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula   =AND($C2<>"",ISEVEN($C2))

Click Format...

Activate the Fill tab.

Select a color, then click OK twice.

 

Repeat the above steps with the formula    =ISODD($C2)   and another color.

@Hans Vogelaar 

Thanks for the reply.

However when I place highlight the cells from C2 down to C72 and then selecting the Conditional Formatting > New Rule then select 'Use a formula to determine which cells to format' and then enter the formula you sent:

=AND($C2<>"",ISEVEN($C2))

I then click Format and select the color & click OK twice (can't click the OK for the color twice).

After clicking the OK for the color an error msg comes up indicating EXCEL thinks I'm trying to create a formula and I need to place an apostrophe before the = so EXCEL doesn't get confused. 

I hit the OK button for the error msg and nothing happens. 

I don't understand what I'm doing wrong.

 

best response confirmed by bailteacher (New Contributor)
Solution

@bailteacher 

Do you use comma as decimal separator? If so, the formula should be

 

=AND($C2<>"";ISEVEN($C2))