Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Conditional Formatting With Empty Cell

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

Hi,
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.
=COUNTIF(AcceptedData;A1)=0

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

View Answers     

Similar Excel Tutorials

List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
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 ...

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 ExcelForum.com: http://www.excelforum.com/excel-gene...ing-blank.html




-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,
Daniel

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




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?

Thanks!

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 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?

Thanks!!


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


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

if("","box","nobox")

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.




Hello

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.

Thanks,
Steph

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.

Regards,

Shuja


Hello,

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?

Thanks
Snap



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!
Alan

Hey people,

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

IF CELL = BLANK DO NOTHING BUT IF CELL HAS DATA LESS THAN TODAY HIGHLIGHT RED

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!

Hello,
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


Hi,

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

Thanks


Hello,

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?


Im using conditional formating to highlight a number of cells in a colum if they are 180 days past the date entered.For example I'm using =(A1+180)<today() to determine this. Now i need to count the highlighted cells. I tried using countif with the appropriate range and the formula I used to for the conditional formatting but it didnt work. Please help!


I am trying to use the conditional formatting feature (of Excel2002) to highlight cells which contain a text string (in this case a last name) from a drop down list I created.
For example, if I select the name "Monroe" from the drop down list, all the cells in my conditional formatting range that contain only "Monroe" in the cell turn green as I want. (I used the cell-- is equal to command in the conditional formatting box). But when the cell contains more than just "Monroe" such as "Monroe/Smith", the cells do not get highlighted. How do I get the conditional formatting to apply to every cell that contains "Monroe" regardless if there is more in the cell or not?

Thanks for any help with this matter.


Hello,

I would like to know how to create a macro to sort through my spreadsheet and highlight blank, and/or data consisting of numbers or words. I have found that conditional formatting does not fulfill the needs of my complex data. Here are where my problems lie:

Column H, will either consist of a number 1-3.
Column I, will have data consisting of variable data such as, "ball", "bat", "cap", and "nachos". "Ball" and "bat" should be associated to "1" in Column H. "cap" should be associated to "2" in Column H, and "nachos" is associated with "3" in Column H.
If Column H and Column I do not match, then those cells should be highlighted.
Column J, if H is either 1 or 2, and if J is blank, or consist of "other" or "done", this should be highlighted.
Column K, if H is either 1 or 2, and if K is blank, this should be highlighted.
Column L, if H is either 1 or 2, and if L is blank, this should be highlighted.

I would then like to have all highlighted cells and have that row copied to a new sheet within the same workbook.

Any help would be appreciated.

Thank you,
Dadomi

I have a spreadsheet that has a couple columns. On the left is numbering and on the right side are properties. I have it setup so that if column B is blank, all the other columns are blank, including the numbering.

What i want to happen though is the blank cells shaded grey. So that if i add something it will come back white and so will the cells in the same row.

I know i have to use conditional formatting and i was thinking of writing something like:
Code:

=(B1="")


The problem i believe lies in the fact that the cells aren't blank, thay have formulas in them. so it doesn't work. even if i change it to =0 it still doesn't work.

any ideas?


Hello,

I have a column of dates that needs to be compared current date and
highlighted if the date is less that today's date.


The dates were populated in a general non-specific format (below) and vary
in length. My plan was to create a macro, that uses conditional formatting
to say; if the cell date is less than today's date highlight it yellow. I
am not finding this to be an easy task! Anyhow, after creating the
conditional formatting; I apply it the column by using a past special, then
lastly apply a format to the entire column of dates as DATE "*3/14/2001".
What happens really doesn't work like expected. The date reverses itself in
the format of 05/31/2006 without applying any highlight for the conditional
format. Does anyone know how, I can get this to work? I don't mind the
date being reversed, but it's not being recognized or highlighted from the
conditional formatting. Help!



The column below should have two dates highlighted in yellow (second and
last).





2006/05/31



2004/09/30



2005/07/08



2004/07/31