Conditional formating of numbers saved as text

**Palabalo** · 03-07-2011, 06:05 PM

Background of problem:

Data the I receive is in the form of numbers stored as text. (Must keep significant figures)
I track this with a formula to return the significant digits..

The final form of the data should be stored as numbers as text. Currently using Fixed() function recalling the significant digits from the beginning.

Problem:
Ok my dilemma, the final thing I need to do is conditional formatting of the data using numbers.

Example of single data entry
Step 1

'324.24 - Received from import

Step 2

Determine digits beyond decimal point =2

Step 3

Use Value() to turn into number.
324.24000

Step 4

Apply Conditional format. (if < 350 and > 300 then green text and Underline)

Step 5

Reapply Significant Digits from Step 2 using Fixed(324.240000,2)
324.24

The issue is once I change the cell back to text the conditional formatting fails.

Thank you in advance.

**TMS** · 03-07-2011, 06:16 PM

Maybe, as your condition:

=AND(VALUE(A1)>300,VALUE(A1)<350)

Regards

**Palabalo** · 03-07-2011, 06:32 PM

So the issue is that I need to apply this to many many cells, How do make conditional formatting copy smart?
How would apply this to many cells
for example
# A B
1 25 35
2 35 22
3 45 58
4 22 48
5 15 9
lb 20 10
ub 25 25

Where Lb and Ub are the lower bound and upper bound of the Conditional constraints.
This table is much larger making individual references time consuming
For clarification A1 would be formatted if it was <20 and >25
and B1 would be formatted if it was <10 and >25

**TMS** · 03-07-2011, 06:42 PM

Change the 300 and the 350 to refer to the lower bound and upper bound cells for the respective columns. So, assuming those bounds are somewhere at the bottom of the columns, you'd make the column relative and the row absolute. For example, A$50 and A$51, for column A. Copying the CF to the other cells should auto adjust.

Regards

**Palabalo** · 03-07-2011, 06:48 PM

Thank you sooo much, I went the total backwards way of getting that information.

I was changing my data instead of changing the conditions.

TY again!