| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
coditional formatting
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 |
|
#2
|
|||
|
|||
|
Hi!
If the default font color is black then you only need one condition: Select the range of cells. FormatCondtional 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 "John Knoke" wrote in message ... 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 |
|
#3
|
|||
|
|||
|
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "John Knoke" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... 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. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "John Knoke" wrote in message ... 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 |
|
#5
|
|||
|
|||
|
Thanks Biff, works great!
"Biff" wrote: Hi! If the default font color is black then you only need one condition: Select the range of cells. FormatCondtional 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 "John Knoke" wrote in message ... 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 |
|
#6
|
|||
|
|||
|
Thanks works great also
"Ragdyer" wrote: 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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... 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. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "John Knoke" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
| conditional formatting question | Excel Discussion (Misc queries) | |||
| Formatting dates in the future | Excel Worksheet Functions | |||
| Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
