Conditional Formatting With Empty Cell

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

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

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