Conditional Formatting for #'s ONLY

[TAPS_MikeDion]

My apologies for my brain being a bowl of pudding right now. Hopefully this question doesn't come across too stupid to ask, but...

All I'm looking to do is to use conditional formatting for a group of cells to change the text color to red if the value in any given cell is =>1. However, there will be cases when an X is present in the cell, which is throwing off my results. Right now anything in a cell formats the color to red. As I was typing this out I was thinking that I may need an ISNUMBER formula to do this?

Thank you for your help!

-Mike

 

[awoohaw]

try:

Book1
	A	B	C
1	value	formula
2	-1	=-2/2
3	0	=0/2
4	1	=1/1
5	0.000001	=1/1000000
6		#N/A	(blank)
7		=""
8	X	="X"
Sheet1

Cell Formulas
Range	Formula
A2	A2	=-2/2
A3	A3	=0/2
A4	A4	=1/1
A5	A5	=1/1000000
B2:B8	B2	=FORMULATEXT(A2)
A7	A7	=""
A8	A8	="X"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=AND(ISNUMBER(A2),A2>=1)	text	NO

 

[TAPS_MikeDion]

Hi awoohaw,
I tried using:

=AND(ISNUMBER(A2),A2>=1)

And made the change to the range I'm using (S3:AD1000), but it didn't do anything to the range of cells I have selected for the conditional formatting.

Maybe I'm not using it correctly?

 

[awoohaw]

for cell S3 enter this conditional formating rule exactly:

=AND(ISNUMBER(S3),S3>=1)

Notice the references are completely relative, no "$" signs.
then copy formatting over the entire range.

Clear all rule first.

 

[TAPS_MikeDion]

That is exactly what I used. I deleted the rule and started over, and got the same results.

Unfortunately, I have to shut down the computer for tonight and hopefully try again tomorrow (or Monday.)

Thank you!!!

 

[TAPS_MikeDion]

Hi awoohaw,
Well, what you suggested DID actually work. The problem I ended up finding was that the cells in question were originally formatted as text, which I suspected was the problem since what you gave me should have worked. I changed their format to "number", but the cells with numbers were still showed errors stating "convert to a number."

The formula WAS working. I simply had to go to every cell containing a number and re-enter the number for it to work.

Thank for the help!

 

[awoohaw]

i'm happy you figured it out. Next time you have a "number as text" situation you can try to use some of these ideas:
1. Use Text To columns.
OR
2. In a random cell type a 1. then copy the cell, then use paste-special-multiply.