Conditional Formatting - Highlight Cells to return Alphabetical only (amongst Alphanumerical entries)

[shauneyd]

Hi all,

First time poster here.

I have a huge column with a mix of both Alphanumeric and Alphabetical entries like so:

G1
G2
G3
A
AA
AB

Using Conditional Formatting I would like to highlight only those cells that contain Alphabetical only and no numbers i.e. A, AA, AB and so on.

I have been playing around with a few formulas but I am having no luck so far. Every formula I use highlight every single entry on the column.

Any help or advice would be greatly appreciated, thank you.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A
1	List
2	G1
3	G2
4	G3
5	A
6	AA
7	AB
8	23Skidoo
9	Bananas
10	Hang10
11	Stop
12
13
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=AND(ISERROR(FIND(ROW($1:$10)-1,A1)),A1<>"")	text	NO

 

[shauneyd]

Welcome to the MrExcel forum!

Try:

Book1
	A
1	List
2	G1
3	G2
4	G3
5	A
6	AA
7	AB
8	23Skidoo
9	Bananas
10	Hang10
11	Stop
12
13
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=AND(ISERROR(FIND(ROW($1:$10)-1,A1)),A1<>"")	text	NO

Hi Eric,

Thank you so much for your kind welcome. I am looking forward to being an active member of the site and hopefully I can help others.

And thank you for the great formula - it works a treat on some test data I tried. I do have an additional problem that I have just discovered though - the Master Spreadsheet data contains blank cells in the column and the formula doesn't work with it. Is there any way I can add to the formula so that when it sees a blank cell it ignores it? I have been playing about with a few statements but not getting it to work unfortunately.

If there is no way of doing this though I'll maybe add a value of zero into any blank cells I encounter.

Thank you so much again for your help, I really am grateful.

 

[Eric W]

Are you sure that those cells are empty? I designed the formula to ignore empty cells (see rows 12 and 13 in my example above).

=AND(ISERROR(FIND(ROW($1:$10)-1,A1)),A1<>"")

The part of the formula in red is what should ignore empty cells. My guess is that you have something in those cells that's not visible, and it would have to have a number in it. Are any of those cells formatted not to show data? Any other Conditional Formatting on that column? If you still can't figure it out, can you show an example of where it fails, using the xl2bb tool that I used? You can see how to download, install, and use it with the link in my signature, or in the reply box.

 

[shauneyd]

Are you sure that those cells are empty? I designed the formula to ignore empty cells (see rows 12 and 13 in my example above).

=AND(ISERROR(FIND(ROW($1:$10)-1,A1)),A1<>"")

The part of the formula in red is what should ignore empty cells. My guess is that you have something in those cells that's not visible, and it would have to have a number in it. Are any of those cells formatted not to show data? Any other Conditional Formatting on that column? If you still can't figure it out, can you show an example of where it fails, using the xl2bb tool that I used? You can see how to download, install, and use it with the link in my signature, or in the reply box.

My apologies Eric. The formula works perfectly. I took my time and did a lot of cleanup of the data (but left the blanks in) so there must indeed have been something that wasn't visible within the data - possibly in white font, who knows. Apologies again Eric and my sincere thanks to you. Thank you for taking the time, I really appreciate it.

 

[shauneyd]

Hi Eric, one last question if you don't mind. How would I amend the formula if I was to highlight the entire row instead of just the cell? I have been trying myself but to no avail unfortunately. I promise this will be the last question I ask on this. Thank you again.

 

[Eric W]

Just extend the range you want it to apply to, and put $ in front of the column with the cell:

Book1
	A	B	C
1	List	Something	Else
2	G1	a	1
3	G2	b	2
4	G3	c	3
5	A	d	4
6	AA	e	5
7	AB	f	6
8	23Skidoo	g	7
9	Bananas	h	8
10	Hang10	i	9
11	Stop	j	10
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:C	Expression	=AND(ISERROR(FIND(ROW($1:$10)-1,$A1)),$A1<>"")	text	NO

 

[shauneyd]

Just extend the range you want it to apply to, and put $ in front of the column with the cell:

Book1
	A	B	C
1	List	Something	Else
2	G1	a	1
3	G2	b	2
4	G3	c	3
5	A	d	4
6	AA	e	5
7	AB	f	6
8	23Skidoo	g	7
9	Bananas	h	8
10	Hang10	i	9
11	Stop	j	10
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:C	Expression	=AND(ISERROR(FIND(ROW($1:$10)-1,$A1)),$A1<>"")	text	NO

Eric, thank you so much. I am very grateful for your time and patience with this. This is perfect. Really appreciate it.