Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers??

**evisu18** · 03-23-2012, 10:05 PM

I have a Macro that highlights cells that contain numbers using conditional formatting.
Numbers <0.8 are highlighted in green
Numbers >1.2 are highlighted in red

But the problem is it treats texts, dates, and blanks as numbers.

Any cell with text is highlighted in red.
Dates written in Jan-10 format are highlighted in red.
Blank cells are treated as zero and highlighted in green.

How do I correct this so that only cells containing numerical values are highlighted accordingly?

Your help will be greatly appreciated.

Thanks in advance.

**DGagnon** · 03-23-2012, 11:27 PM

are you doing this with a macro, or just standard conditional formating? if it is a macro could you include the code.

also note, dates no matter how they are formated are actualy a number at the formula level, for example 10-Jan-2012 is actualy 40918.

**evisu18** · 03-24-2012, 12:00 AM

I made this using Recorded Macro and Conditional Formatting. So there is no way to exclude the dates from this conditional formatting?

Thanks

Please Login or Register  to view this content.

**DGagnon** · 03-24-2012, 12:13 AM

Recorded macro's are enharently unperfect, but give this a shot

i added a few more paramaters to your conditional formating

=AND(A1>1.2,A1<40000,T(A1)="",A1<>"")"

A1>1.2 - Your goal
A1<40000 - Makes sure its not a date
T(A1)="" - Make sure its not text
A1<>="" - make sure its blank

Please Login or Register  to view this content.

**evisu18** · 03-24-2012, 07:10 PM

Thanks, but now it does not do anything from what I can tell. I'm running Excel 2010 if it matters.

**DGagnon** · 03-24-2012, 08:25 PM

code error.. try this

Please Login or Register  to view this content.

**evisu18** · 03-24-2012, 09:21 PM

Works great.

Thanks!

**DGagnon** · 03-24-2012, 09:39 PM

no problem

note for future consideration: to use a " in VBA within a string, enter it as a "" that is what my error was in the first run of code.

glad to have been able to help

**DGagnon** · 03-24-2012, 09:40 PM

no problem

note for future consideration: to use a " in VBA within a string, enter it as a "" that is what my error was in the first run of code.

glad to have been able to help

**evisu18** · 03-26-2012, 01:50 PM

Thanks again! I was wondering how can I make the >1.2 to fall within 9.9? I do not want it to highlight anything greater than 9.9.

**DGagnon** · 03-26-2012, 02:16 PM

just change A1<40000 to A1<10 like this:

Please Login or Register  to view this content.

Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers??

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers??

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions