Excel conditional formatting - Highlight numbers greater than 5 but ignore any formula

excel ms-office openoffice.org conditional-formatting
JamesZeinzu picture JamesZeinzu · Nov 7, 2013 · Viewed 14.2k times · Source

I am having a strange problem, I have a column with this formula in each row respectively:

=IF(C6="";"";D6-LEN(B6))

It works great, if the cell next to it is empty, make it an empty cell, if not, show the calculated length. However, when I try to use conditional formatting to make it red when the length is greater than 5, it highlights the cells that are 'blank'(have a formula in but are displaying blank). Any ideas as to why this is happening would be greatly appreciated.

PS. I am currently using Open Office 4.0.1 and it seems to work fine, but when I send the document to a client(Excel 2010) it highlights the empty cells. I have tried clearing the conditional formatting and using Excels built in conditional for-matter and it does the exact same thing.

Thanks in advance, James.

Answer

Siddharth Rout picture Siddharth Rout · Nov 7, 2013

Use this formula for conditional formatting (Tested in MS-OFFICE and not Open Office)

=AND(E1<>"",E1>5)

Note: Replace , with ; in your formula

Screenshot

enter image description here

EDIT

TRIED AND TESTED IN Open Office 4.1.3.2

enter image description here

Related questions

C#: Getting the number of rows/columns with ExcelPackage

I need to read and write data from an Excel spreadsheet. Is there a method to finding out how many rows/columns a certain worksheet has using ExcelPackage? I have the following code: FileInfo newFile = new FileInfo(@"C:\example.xlsx"); …

Read More
How to save .ods spread sheet (OpenOffice.org) to .xlsx (MS Office Excel 2007)

Recently I switched from MS Office to OpenOffice.org 3.4.1. Here are my questions: I was not able to save .ods spread sheet to .xlsx 2007 Format. The option which I got when I use Save As was Microsoft Excel 97/2000/XP (.xls) (*.…

Read More
What is a correct mime type for docx, pptx etc?

For older *.doc documents this was enough: header("Content-Type: application/msword"); What mime type should I use for new docx documents? Also for pptx and xlsx documents?

Read More