Conditional formatting by formula or VB code

**Abhijit2011** · 08-17-2011, 08:12 AM

Hi ExcelGURUs,

I have a spreadsheet where lots of conditional formatting are required.
I want to do it using formula or VB code. For example in A column I have both number and text value (each value is coming from a vlookup formula from a diff tab of the same spreadsheet). I want to highlight only the numbers with a different colour.

If anyone can help me with this….I can try the other requirements.

**NBVC** · 08-17-2011, 08:20 AM

Select the column A range and go to Format|Conditional Formatting, select Formula Is from drop down menu and enter formula: =ISNUMBER(A1+0)

Click Format and select colour from the Pattern tab.

Click Ok
Click Ok to finish.

**Abhijit2011** · 08-18-2011, 05:40 AM

Thanks. This is working but 1 problem...it is highlighting the blank cell also…I don’t want the blank cell to be highlighted.

**arthurbr** · 08-18-2011, 05:48 AM

Same method as NBVC but try =isnumber($a1)

**NBVC** · 08-18-2011, 07:47 AM

I added the +0, because usually when there is a mix of text and numbers, it could be that the numbers are text formatted.. the +0 forces the number to be a number, if indeed it has not alpha characters in it.... so arthurbr's method may or may not work...

or you can try: =AND(A1<>"",ISNUMBER(A1+0))