Excel conditional format (1 Viewer)

[belljr]

So I have set of data in column C.

each 2 cells relate to each other. So for example c1 and c2 go tpgether, c3 and c4 etc

so what i am doing is if cell 1 cell 2 difference is 5 or less 5 I want cell c1 to be yellow.

if it's greater than 5 red.

I have it to format I think but I'm not sure how to apply that to each set of data easily

 

[Bogeys]

So I have set of data in column C.

each 2 cells relate to each other. So for example c1 and c2 go tpgether, c3 and c4 etc

so what i am doing is if cell 1 is greater or less than cell 2 by 5 I want cell c1 to be yellow.

if it's greater than 5 red.

I have it to format I think but I'm not sure how to apply that to each set of data easily

I may not be understanding, but it seems like you are saying the same thing in both...did you mean less than yellow and greater than red?

Under conditional formatting you can use the formatting cells that contain and it has a less than and equal to, etc conditions. Just add more than one formatting condition to get what I think you saying above and copy the formatting to the rest of the cells that need it.

There is probably an easier way to do it, but that is my first thought.

 

[belljr]

Hmm poor wording. If the difference between the cells is less than 5 yellow, greater red.

 

[tjnc09]

Go to home-> Conditional Formatting

New Rule

Use formula

=IF(ABS(C1-C2)<5,"TRUE","FALSE")

Then choose appropriate color below

Do it again with New Rule for your second condition

 

[belljr]

Ok that's great and kind of what I did.

how do I apply that to the entire column?

 

[Ignoratio Elenchi]

You only want the formatting to apply to C1, C3, C5, etc? You could do something like this:

=IF(MOD(ROW(C1),2)=0,FALSE,IF(ABS(C1-C2)>5,TRUE,FALSE))

 

[tjnc09]

So every odd numbered row has the conditional format and every even numbered row does not?

 

[belljr]

I want the format to apply to c1 and c2.

then c3 and c4

c5 and c6.

etc etc.

 

[Ignoratio Elenchi]

Unsolicited advice: Not sure what this is for or how much control you have over it, but it sounds like the data is laid out pretty poorly. If you're comparing values you should have them side-by-side in two different columns or something, not in odd- and even-numbered cells in the same column.

 

[belljr]

Unsolicited advice: Not sure what this is for or how much control you have over it, but it sounds like the data is laid out pretty poorly. If you're comparing values you should have them side-by-side in two different columns or something, not in odd- and even-numbered cells in the same column.

its an import from a text document that is saved from a gui application.and belive it or not it's easier on top for what we are doing

 

[Ignoratio Elenchi]

In cell C2 you could add the following formatting:

=if(mod(row(C2),2)=1,if(abs(C3-C2)>5,true,false),if(abs(C1-C2)>5,true,false))

and then copy it to all the other cells. I'm just not sure, depending on your version of Excel, how it will like this formula being copied into cell C1.

 

[Ignoratio Elenchi]

C1 has no conditional formatting,

C2 has conditional formatting,

Right?

It sounds like he wants the formatting in every cell:

I want the format to apply to c1 and c2.

then c3 and c4

c5 and c6.

etc etc.

 

[bryhamm]

So, do you only want cell C1 to show yellow or red? Or do you want C2 to do so as well?

Either way, format C1 how you want (and format C2 if you need). Then, highlight both C1 and C2. Choose copy, then start in C3 and highlight all the way down as far as you need, but making sure you have an even number of cells chosen. Then, paste just the formatting. C1 formatting will go to C3, C5, C7, etc and C2 formatting will got to C4, C6, C8, etc

 

[bryhamm]

C1 has no conditional formatting,

C2 has conditional formatting,

Right?

Highlight C1 and C2. Hit "Copy". Highlight the entire column. Right click and select "Paste Special", and in the popup, select "Formats".

Every odd cell will have no formatting

Every even cell will have the C2 rule.

or what this guy said

 

[belljr]

Paste formatting worked. Thanks all