Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Conditional Formatting With Empty Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I want to use conditional formatting on a column ('A') to highlight cells that do not have a match with any cells in a specific range.
So I've made a range called 'AcceptedData', which contains a couple of cells with text.
Also included a blank cell in the range, since I do not want blank cells to be highlighted.

Then I used this conditional format forumla on column A.

But it doesn't seem to work. The blank cells get highlighted.
How do I get around this?

View Answers     

Similar Excel Tutorials

Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
Shade Every Other Row in Excel Quickly
How to shade every other row in Excel quickly without using the Table feature. This method is for when you simply w ...
Easily Compare Duplicate Values in Excel
Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Format Cells as a Fraction in Excel Number Formatting
- This free Excel macro will automatically format a selected cell or many selected cells in the Fraction number format in
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Format Cells in The General (default) Format in Excel Number Formatting
- This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the defa

Similar Topics

Hi everyone -

My project today has been figuring this formula out and... it's not going well :P

What I need is to use conditional formatting in cell A2 to accomplish the following:

"If for the range C2:H2 any cells are NOT blank, check cells C$1:H$1. If ANY of those corresponding cells in row 1 are blank, highlight cell A2 red."

I'm going to apply this formula in Column A for any customers added to the list (hence the $ in the C$1:H$1 reference). So cell A2 would NOT be highlighted because all quantities in cells C2:H2 have a corresponding lot number above. Cell A3 WOULD be highlighted because cell D3 does not have a corresponding number in cell D1. Does that make sense?

Also posted on

-I have two columns of numbers.
-There are blank cells in each column that appear sporadically.
-In each row of numbers, I want to create a conditional format that highlights a cell if the number in the cell is greater than a designated value.
-Currently I am using: Conditional formatting 'formula' =ABS(C4-C3)>.1 OR =ABS(D4-D3)<.1 for the respective column of numbers.
-This formula does not work now that I have empty cells between the values. All the blank cells are highlighted and the subtraction can not take place if the cell is blank.
-I want the formula to not see the blank cells and make the comparison between the first vale and the next vale in the series.
-I do not want to sort the blank rows out and then replace.
Thank you,

Please see attachment: TESTAE042701.csv

I am using a conditional formatting setup to highlight blank cells in a column.

The formula is simply "Cell value < 1"

However, I do not want blank cells beyond the end of the data range to be highlighted. At present I have aroun 2500 rows, but this changes constantly (I am always adding and removing lines), so I need a dynamic way of specifying the end of the data range. At present the formatting rule is applied to $A:$A

Hello - Me again.

I am trying to ensure any cells with a zero (00) are not dispalyed. The formula I am using is =IF(X2=0,"",X2). This works fine, except when it comes to conditional formatting.
As you can see from the table below the red highlighted cells are the ones with 00. However, I do not want this to highlight the cells with 00 I need it to stay balnk.

My conditional format is:
Format only cells that contain - cell value - greater than - ==0.0104166 (15)

So why does the cell highlight red? Any help is much appreciated.

2nd Break 18 14

In Excel 2010, hoping to highlight all the cells in a range (about 100 cells) that match whatever's in the cell I'm currently in. All cells contain plain text (cells in the range being highlighted, and cells in the range being navigated with the cursor, and the two ranges do overlap).

Been fumbling around with variations on INDIRECT(), CELL(), ROW(), and COLUMN(), ... but no luck. I seem to get either everything always highlighted, or nothing ever highlighted.

Any help greatly appreciated!

Hello. I am trying to use conditional formatting and combine the value of three cells, and based on the total of the cells, highlight all the cells with a fill color. Example:

A4 = 450
A6 = 150
A8 = 350

If the total of the cells is greater than 700, then I want all cells to be highlighted in red. If the total value of the cells is less than 300, then I want all cells to be highlighted with green. If the total value is between 301 and 699, then I want all the cells to be highlighted with yellow. (There is a reason for no data being in cells A5 and A7 - these are blank and will always be blank)

I tried using a formula "=SUM(A4:A8)>700" for one condition, but the result is cell A4 is the only cell highlighted with red. What formula (or formula's) do I use so all three cells are highlighted?


I have a list of values in column D and another in column A

I want to highlight all the cells in column D that appear in column A

To do this I am using conditional formating with this forumla:
In cell A2: =COUNTIF(D:D,A2)
In cell A3: =COUNTIF(D:D,A3)
In cell A4: =COUNTIF(D:D,A4)

Some cells are being highlighted but some that are in col D and col A are not being highlighted. I have attached the spreadsheet if anyone wants to have a look.

Two examples of it not working are cell D33 which appears in col A but is not highlighted. D47 & D58 also arent highlighted. There may be others but I havent checked them all.

If anyone could have a look that would be great.


I have the conditional formatting formula =COUNTIF($C$2:$C$4177,C2)>1 to highlight duplicates in column C. The condition is found in cells C2:C4177.

How do I get it to exclude the condition for a blank cell, or for a cell value of zero? When more than one zero is in the range, then the zero's and all blank cells get highlighted with the format condition (highlighting the cells red).

I have tried to create another condition of [Cell value is equal to 0] and [Cell value is equal to ""], and set the format to the default white background, but this doesn't work when there are duplicate zero's in the range.


I have a couple of blank cells in a column that are blank and I want them highlighted yellow because they are blank. I have highlighted the column gone through the Conditional formatting route and selected 'formula is' followed by ="COUNTBLANK" followed by highlighting the cell yellow, but I've had little joy. Are you able to help?

I am trying to write a macro that uses conditional formatting on my data (which is columns A to AE wide, and varying rows deep) to highlight an entire row (from A to AE) if there is a cell in the row which is highlighted.

ie. I have conditional formatting set on different columns to highlight cells with errors and cells that are blank. What I would like is to have the entire row highlighted if highlighting already occurs on a cell in that row.

Is there an easy way to do this?


I have tried to set up a list of cells to highlight in red any numerical values which are greater than 0 using conditional formatting.

This works fine, except that all blank cells are also highlighted in red.

Formula is currently:

cell value is greater than 0

What do I need to do to ignore the empty cells?

Thanks in advance

Hi all.
On my sheet I am doing a conditional format where a3:r34, get formatted based on the date in col a.
right now i have a3 to r34 highlighted, and this is the formula in the conditional formatting
=OR(WEEKDAY($A33)=5,WEEKDAY($A33)=6). ((i know this highlighted THUR and Friday, I am in Saudi Arabia, they are their weekends))

Now I want to ADD another Conditional format, I want to Highlight the same data on the sheet (a3-r34), but this time, I want to use the information in COL B.
If cell b3 =OFF, I would like to do this down the whole sheet (a3-r34).
It works fine until the Highlighted CELLS from the other conditional format don't let the second conditional format do its thing.
Should I use one formula, (in the conditional format), if so any suggestions?
But I would like the OFF cells to be different than the first CF.
Not to sure how this will work if it does.
thanks in advance

I am trying to apply conditional formatting to a range of cells, let's say A1:C3.

I want the cells to have a box around them if they are not blank.

I can do this for once cell with no issue, however when I try to apply it to multiple cells in a range (or named range of the same cells), the formatting is applied if any of the cells are not blank.

I need each cell to apply formatting if and only if that cell is not blank.

I thought a simple formula like


would accomplish this, but it isn't working.

Box and no box are not going to apply the format of course, but you know what I mean.


I used Conditional Formatting to highlight cells containing specific values (i.e. less than, between, etc...). I now need to show a count of how many cells are green, red, yellow, etc...

I have been reading about having to create a User Defined Function using VBA, but after various tests, I can only achieve the desired results if I manually highlighted the cells (i.e. highlighted the cell and filled it with colour).

How can I count the cells which were highlighted using Conditional Formatting?

I am using Excel 2007.


hi all,

i am having a real mad day, i am looking a creating some conditional formating on my spreadsheet that will do the following

data is entered in column a1-a6000 ect... saying, test 1, test 2, test 3, test 4, test 5
for each of these i need to highlight the row but only specific cells in that row.

example ..
test 1 in cell a1- highlight b1,c1,g1
test 2 in cell a2 highlight b2,e3,f2
test 3 in cell a3 highlight b3,c2,h3

this conditional format only occurs when data is added to any of the cells in column a - so inturn the validation will check each row and highlight the specific cells as above. if no data in column a then the validation wont run, however if data is then added to the highlighted cells, (highlighted by the conditional formatting) then the highlighted cells for that row no longer becomes highlighted.

i am really racking my brains on this one - i googled this for ages

thanks for looking

I was able to highlight some cells using conditional formationg.
Unfrotunately the cells are not next to ehac other (there are blank
cells in the middle). Since I don't need those blank cells I can just
delete them or sort the column so all the cells with information move
to the top, the problem is that when I finish sorting and the cells
move up I lose the format I applied on conditional formatting.

is there any other way to apply format (change color) to a cell
depending on 2 other cells?

This is what I have:
Highlight in bold row 2 if A2 -a date- is between A1 -date #1- and
A2 -date#2-

However I want to maintain that format (bold highlight) even if I move
A2 to a separate column. Does it make sense?

Hi Guys,

i'm hoping there is a quick solution to this problem:

I have created a conditional format which highlights the cell in red if the value is less than 40. This work fine but the conditional format activates before a value is entered (i.e. even if a value is not entered the cell appears highlighted in red).

I want to create conditional format that only highlight the cell red when a value is entered into the cell by the user. If the cell is blank or empty then i don't wnat the conditional format to activate (or i would like to have a conditional format to restore to the default colour).

Is there a way i can capture blank/empty values in a cell when using the conditional format?
I don't mind whether it talkes one or many conditional formats to do what wnat to, as long as it works correctly.




I am trying to use conditional formatting in a dynamic named range, where the I want to highlight cells that are either blank or are not found in a table.

The dynamic range is called DN, and in the conditional formatting formula I use =ISBLANK(DN)

I've attached an example, and in this, no cells get colored. Surprisingly, in another workbook of mine, ALL the cells in the range get colored orange! What am I doing wrong?


Hi all

I'm having a conditional formatting problem I'm hoping someone can help.

I have a range that contains dates, with some cells within containing text (e.g. "TBC" or "planning will occur wk42"). I have basic rules as below
- highlight red if older than today
- highlight green if today or in the future

The problem I have is that the cells that have text are highlighted green which I don't want. I also don't want to have to go through the range removing conditional formatting from each of those cells each time I copy over new data. Is there a way of telling excel to not use conditional formatting on those cells that contain text so I can manually highlight as I need? I can't help that some have text as that is how I receive the data.

Thank you!

Hey people,

i'm trying to use conditional formatting to carry out the following process;


I'm having problems with making the cell do nothing if it's blank. Also, i'm currently running another conditional format to highlight cells green if they are blank.

So, I need to;

If the cell is blank = green
if the cell is a date past today = red

Am i correct in having two conditional formats going on. One for the blank cell and the other if it's got info in? I seem to be having conflicting formats, I guess. It would work if the CELL >=TODAY() hightlight red didn't make the cells red if they are blank.

I hope this is clear. Please help!

Hey all this should be an easy one, but my brain isn't working today.

So I have two columns of data: columns A and B

I want to have conditional formatting highlight cells in column B that are not an exact match of the adjacent value in column A.

So A2 = 13109 and B2 = 13109. it's a match, so don't format it
but A3 = 2500000 and B3 = 0. Since there is a discrepancy I want this highlighted (and I want the highlight to show up in the B column). I guess it could also work to have both cells be formatted where there is a discrepancy if that's easier.

What worked for me originally was to highlight A2:B2 and apply conditional formatting for unique values, but then I would have to do it manually for A3:B3, and A4:B4, one pair of cells at a time.

I know there is a better way, but I just can't figure it out at the moment.

thanks in advance!

In column F I have a data validation Yes and No. (I have attached the spreadsheet) Because there are technically only 3 options in column F (yes, no and blank) My conditional formatting has the cells fill wherever there is a 2. I would like a fourth conditional format so if E = No then leave the cell in column H Blank. I( tried a conditional format =$E:$E="", but it didn't work.) So the cells are not filled all the way down. I'm not sure if I explained this well but I think you'll understand when you open the file.

Thank you


I have a range of data where some cells have numbers. I need to highlight duplicate numbers. Example: if the values in the list are
200, 150, blank, blank, 200, blank, 300
then the two instances of 200 should be highlighted.

I have Excel 2007. I used the conditional formatting > highlight duplicate values but it also highlights the blanks thats why I need a formula



I am trying to search a range and have the cells highlight bases on a cell reference and wildcard using Conditional formatting. The range contains names such as John, Bob, Fred, etc.

This is no problem, however, some cells contain 2 names such as John and Bob.
I have a validation list in A1, when I select a name from that list, I want to search the range and have all cells with that name (alone or with another) have a condition applied (highlighted). Single names work fine, double names do not.

I've been able to do countif formulae using a wildcard =countif(range,"*"&A1&"*") but cannot get this to work with conditional formatting.

Any suggestions?

Thank you

I am trying to make a conditional formatting formula that applies formatting based on whether the column A cell has a value.
Basically, if the conditional formatting is applied to cells G3:L3 and A3 is blank (or value is blank/0) then apply formatting.

I have attached an example (that doesn't work this way)

How can I change the formula to do this?