Conditional Formatting With Empty Cell


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?
Similar Excel Video Tutorials
Conditional Format w DATEDIF function
 See how to use Conditional Formatting and the DATEDIF function to add formatting to a cell based on the content in a different cell. See how to create ...
Conditional Format Based On Diff Cell
 See how to create conditional formatting based on a different cell. See how to highlight (add formatting to a cell) a number based on a date in a diff ...
Gantt Cell Chart Conditional Formatti
 See how to use Conditional Formatting to create a Gantt Cell Chart. See how to use Conditional Formatting to highlight a range of cells that space a c ...
IF Function and Conditional Formatting
 See how to use the IF function and Conditional Formatting to show the words Warning with a red format when a cell value is too low. Learn about how th ...
Helpful Excel Macros
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
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/excelgene...ingblank.html
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
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)
etc
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.
http://londonlabs.co.uk/test/countif_not_working.xls
If anyone could have a look that would be great.
Thanks.
Hello
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 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.
Hello, I'm new at this forum and in need of help.
I'm trying to compare one range of cells (RANGE 1) with another range of cells (RANGE 2), using conditional format I would like to highlight the cells with the same value in RANGE 1 and RANGE 2.
So I select RANGE 1 > Conditional format >"Formula is" and fill in: "=or(value(A1)=value(RANGE 2))". With A1 being a cell of RANGE 1, give it a color format and apply.
The cells in RANGE 1 that have the same value in RANGE 2 are now highlighted. So far so good.
But if I do the exact same thing while recording a macro, and I start the macro, only A1 is highlighted and the other cells that should als be highlighted are not. They are only highlighted after I open the conditional format in each individual cell and just pressing ok.
You can see it in the excel file I'm attaching.
Thanks
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!!
Hello,
I am a not very experienced user of excel 2007.
I have a row of figures in a table, some cells may be blank. I total the highest 3. With help from past forum threads I have achieved this.
I now want to conditionally format these 3. Ideally I would prefer to format the non blank cells that are not included in the best 3.
The problem with using the standard selection of largest is that if the 4th highest is the same as the third, 4 are highlighted (1st row in attached).
Specifying bottom no. is worse due to empty cells (row 2).
My formula for totalling does not work when inserted into conditional formatting.
Some help much appreciated.
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
http://img204.imageshack.us/img204/9...celproblem.jpg
Hi everyone 
My project this afternoon 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?
[IMG]http://img204.imageshack.us/img204/9244/excelproblem.jpg{/IMG]
Hi everyone 
My project this afternoon 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?
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 (a3r34), 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 (a3r34).
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
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.
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 a1a6000 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.
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
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!
I'm trying to figure out how to do some conditional formatting only if the cell above the cell that I'm formatting has a value. Anybody know how to do this? I know it's probably going to use an IF statement, but I'm having trouble figureing out how IF statements work in conditional formatting.
Example:
Name.....AWT2....AWT3....AWT6.[BLANK CELL]
Dude........X......................X..........
What I'm trying to do is set this worksheet up so that people can add to the row containing AWT tasks without me having to do the conditional formatting for them. So the cells that contain an X should be green (figured that out), and the cells that DON'T contain an X should be red. I figured that out, except then the red cells continue to the right... If I could make it so the red cells don't turn red if there is a blank cell above them, then it would work for what I need.
I hope I explained this well enough...
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
Example:
Cells A1:A4 have conditional formatting set up that states if they are equal
to cell A10 they will be highlighted yellow.
Is there a way to quickly see what cells drive conditional formatting? In
other words, we can use Edit > Go To Special to see which cells have
conditional formatting applied; however, this doesn't show us that A10 is
involved. Since there isn't a formula directly involved, we can't trace
precedents/dependents. Can we only know that A10 is involved in the
formatting of A1:A4 by selecting those cells and going into the conditional
formatting dialog box? Is there another, faster way?
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?
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 13.
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