Yet another cell formatting question

Tirolyn · Apr 4th, 2002, 09:07 PM

I'm sure I'm making this harder than it really is but here's my wish list. I have columns that include some text cells and some data cells. "I wish" for a way to have the font color change to red if a data entry isn't within a certain range. I want to format the entire column this way because I don't know how many entries will be in the column. What I don't wish is for the cells with text to change colors. I know could easily get the data to change to red by highlighting the entire column and using conditional formatting but that changed the cells with text in them to red as well. Example:

Job No.
Lot No.
.305 ±.005
.301
.031
.306
.307
Pass
Pass
.308

So all data in this column between .300 and .310 would be black, text would be black but data <.300 or >.310 would be red.

Dave Patton · Apr 4th, 2002, 09:32 PM

Use Conditional Formatting

Formula Is

=AND(ISNUMBER(A8),OR(A8<=0.3,A8>=0.31))

Check the threshold do you want the >= or just > and <= or just
Extend or use Format Painter for other cells

[ This Message was edited by: Dave Patton on 2002-04-04 20:35 ]

[ This Message was edited by: Dave Patton on 2002-04-04 20:42 ]

Yogi Anand · Apr 4th, 2002, 09:40 PM

Hi Tirolyn:
Try the formula is:
=AND(ISNUMBER(A5),OR(A5<0.3,A5>0.31))

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

Yogi Anand · Apr 4th, 2002, 09:42 PM

Hi Dave Patton:
Sorry!, my post may have been just behind yours.

Dave Patton · Apr 4th, 2002, 09:47 PM

No reason to say sorry.

Great minds must think alike.

I was going to edit my formula and comments after I read the information a second time.

The >= should be > and <= should be <.

Yogi Anand · Apr 4th, 2002, 10:07 PM

Good Job Dave ... Cheers!

Tirolyn · Apr 5th, 2002, 12:01 PM

Dear Guru's,
You guys are the BEST!! Yes, it works just fine. What a dope I am for forgetting about the format painter..duh.. wouldn't really have mattered though since I didn't know the formula. Anyway I'm thrilled to have found this wonderful resource! I'm sure I'll be back scanning the boards and bothering you more as I try to "idiot proof" our records from data entry errors. Thanks so Much

Apr 4th, 2002, 09:40 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Tirolyn: Try the formula is: =AND(ISNUMBER(A5),OR(A5<0.3,A5>0.31)) Please post back if it works for you ... otherwise explain a little further and let us take it from there! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 09:07 PM	#1
Tirolyn New Member Join Date: Apr 2002 Posts: 2	I'm sure I'm making this harder than it really is but here's my wish list. I have columns that include some text cells and some data cells. "I wish" for a way to have the font color change to red if a data entry isn't within a certain range. I want to format the entire column this way because I don't know how many entries will be in the column. What I don't wish is for the cells with text to change colors. I know could easily get the data to change to red by highlighting the entire column and using conditional formatting but that changed the cells with text in them to red as well. Example: Job No. Lot No. .305 ±.005 .301 .031 .306 .307 Pass Pass .308 So all data in this column between .300 and .310 would be black, text would be black but data <.300 or >.310 would be red.

Apr 4th, 2002, 09:32 PM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Use Conditional Formatting Formula Is =AND(ISNUMBER(A8),OR(A8<=0.3,A8>=0.31)) Check the threshold do you want the >= or just > and <= or just Extend or use Format Painter for other cells [ This Message was edited by: Dave Patton on 2002-04-04 20:35 ] [ This Message was edited by: Dave Patton on 2002-04-04 20:42 ]

Apr 4th, 2002, 09:42 PM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Dave Patton: Sorry!, my post may have been just behind yours.

Apr 4th, 2002, 09:47 PM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	No reason to say sorry. Great minds must think alike. I was going to edit my formula and comments after I read the information a second time. The >= should be > and <= should be <.

Apr 4th, 2002, 10:07 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Good Job Dave ... Cheers!

Apr 5th, 2002, 12:01 PM	#7
Tirolyn New Member Join Date: Apr 2002 Posts: 2	Dear Guru's, You guys are the BEST!! Yes, it works just fine. What a dope I am for forgetting about the format painter..duh.. wouldn't really have mattered though since I didn't know the formula. Anyway I'm thrilled to have found this wonderful resource! I'm sure I'll be back scanning the boards and bothering you more as I try to "idiot proof" our records from data entry errors. Thanks so Much