Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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




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?

Thanks!

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


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


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


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

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

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.

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

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


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!