Conditional Format for a number

[Livin404]

I would like to have a 10 digit number that only includes numbers. The formula that I'm currently using will flag anything over and under 10 digits. However, it will not flag anything other than a number like text or a space. Because what this document is used for I don't want to use data validation for this project. The formula I'm currently using is =AND(ISNUMBER(--C2),LEN(C2)<>10). I'm using the not equal because if this is filled out in error I need it highlighted. Also not critical but if I can have the 10 digit number lead off with a 1, it would be fantastic.

Thank you,
Sheridan

 

[Livin404]

That code worked flawlessly, perfect once again. I’m very humbled in your capabilities. I am trying here, and I am able sort some of this stuff out by myself it just takes me hours. I want to ask if you can extend a little more help, but at the same time I don’t want to be burdensome. In the first Column I got several codes since it has to be done in an exact format. I got codes to remove, punctuation, apostrophes, and a code to find if there is no middle name =ISERROR(FIND(" ",A2,FIND(" ",A2)+1)), and a code to find if there are any extra spaces =LEN($A2)>LEN(TRIM($A2)).

Now the last code I need for that Column is to prevent anyone going out further than a Suffix in a name. For example my name would be Whiteside Sheridan Ulysses III. Everything I need in the cell is exactly the way I need it, with the exception of someone trying to add a fifth block of details. I just need it to error out if someone were to add on additional block of letters.

The last thing I need would be able to highlight blanks cells based on the lowest filled row in Column A. If I had a cell with data in A10, then I would need a blank cells highlighted A2:H9, J2:V9, X2:X9. Would that affect the Conditional Formatting Rule I have for blanks, I am hoping I can just sequence the rules? I provide an example.



Thank you so much, again.

 

[Livin404]

Oh I realized I meant the highlight to go down to row A10 the reason would be Column A is the base column and everything to the right and up minus columns I and W needs to have data down to including the last row with data in Column A.

 

[Eric W]

Maybe:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4
5
6
7
8
9
10	Whiteside Sheridan Ulysses III
11
12
13
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:X22	Expression	=AND(ROW()>1,COLUMN()<>9,COLUMN()<>23,ROW()<=MIN(IF($A$2:$A$1000<>"",ROW($A$2:$A$1000))))	text	NO

For performance, you may want to limit the number of rows you apply this to, as well as the number of rows ($A$2:$A$1000) that you search to find the first value.

 

[Livin404]

I tried it and it works spectacularly on an empty sheet, I tried it on mine where I had numerous entries with lots of formatting rules and had no success with it. A lot of the codes I used were Iferror which made me use a conditionally format for blanks with Stop. I was seeking false with many of my formula which caused it to highlight all blank cells in the column with various colors that led to me creating those rules for blanks. With all these rules is another one that I can use for blank cells no lower than my last entry in column A? So sorry about this, I attached a "sample" of some of the codes I used, I don't know if they many anything.
Thank you so much.

 

[Eric W]

Those are all CF rules? No wonder you're having issues. I can't go through all those and try to figure out which conflict or not. You'll have to decide which rules are more important, and sort them in the Manage Rules box. Use the Stop option as needed. When you start getting into these sort of problems, it's probably time to start thinking if there's a better way to do things.

 

[Livin404]

Got it thank you!