coditional formatting

[Guest]

How can I do a coditional formatting of “n†cells in column where the cell
number is divided by 100,000 and if the decimal number is between X.1 and X.4
format the cell number in “redâ€. If the decimal number is between X.0 and X.5
format the number in “blackâ€

Divide each cell by 100,000

A1 117,176 1.2 Red A1
A2 218,205 2.2 Red A2
A3 288,242 2.9 Black A3
A4 375,224 3.8 Black A4
A5 454,227 4.5 Black A5
A6 535,557 5.4 Red A6
A7 612,154 6.1 Red A7
A8 694,623 6.9 Black A8

Thanks for any help

 

[Biff]

Hi!

If the default font color is black then you only need one condition:

Select the range of cells.
Format>Condtional Formatting
Condition 1
Formula is:

=AND(ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))>0.09,ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))<=0.4)

Set font color to RED

If the default font color is something other than black, after setting
condtion 1:

Add
Condition 2
Formula is:

=AND(ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))>=0,ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))<=0.5)

Set font color to BLACK

Biff

 

[Ragdyer]

Click on "Formula Is" and try this:

=MOD(ROUND(A1/100000,1),1)<=0.4

Set format for RED.

Black will be the default for the other values.

 

[Ragdyer]

Forgot about the zero also returning false, so try this:

=AND(MOD(ROUND(A1/100000,1),1)>0,MOD(ROUND(A1/100000,1),1)<=0.4)

 

[Guest]

Thanks Biff, works great!

Hi!

If the default font color is black then you only need one condition:

Select the range of cells.
Format>Condtional Formatting
Condition 1
Formula is:

=AND(ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))>0.09,ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))<=0.4)

Set font color to RED

If the default font color is something other than black, after setting
condtion 1:

Add
Condition 2
Formula is:

=AND(ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))>=0,ROUND(A1/100000,1)-INT(ROUND(A1/100000,1))<=0.5)

Set font color to BLACK

Biff

 

[Guest]

Thanks works great also

Forgot about the zero also returning false, so try this:

=AND(MOD(ROUND(A1/100000,1),1)>0,MOD(ROUND(A1/100000,1),1)<=0.4)