Conditional formatting in empty cells

Using Conditional Formating from the Format menu I am setting the Pattern for 
cells containinig a number greater than 6 to Green. This works fine except 
empty cells i.e. containing no data, go green.

Has any one got any suggestions?

Best wishes

Nick
0
NickHorn (2)
2/25/2007 4:48:05 PM
excel 39879 articles. 2 followers. Follow

2 Replies
481 Views

Similar Articles

[PageSpeed] 35

Are you sure the cell is empty?

Or do you put a space character in that cell--or do you have a formula that
evaluates to ""?

If it's a formula that evaluates to "", you could use a rule like:

Formula is:
=AND(ISNUMBER(A1),A1>6)



Nick Horn wrote:
> 
> Using Conditional Formating from the Format menu I am setting the Pattern for
> cells containinig a number greater than 6 to Green. This works fine except
> empty cells i.e. containing no data, go green.
> 
> Has any one got any suggestions?
> 
> Best wishes
> 
> Nick

-- 

Dave Peterson
0
petersod (12005)
2/25/2007 4:51:39 PM
Hi Dave

Thanks for getting back to me.  The cell is definetly empty.  

For information , however you have solved the problem and another one I'm 
suffering from.
I just checked the cells format and it's General.  I changed it to number 
and then applied the Conditional Format and it works fine.  If apply Number 
format after applying the conditional format the pattern stays green.

So many thaks for your help.

"Dave Peterson" wrote:

> Are you sure the cell is empty?
> 
> Or do you put a space character in that cell--or do you have a formula that
> evaluates to ""?
> 
> If it's a formula that evaluates to "", you could use a rule like:
> 
> Formula is:
> =AND(ISNUMBER(A1),A1>6)
> 
> 
> 
> Nick Horn wrote:
> > 
> > Using Conditional Formating from the Format menu I am setting the Pattern for
> > cells containinig a number greater than 6 to Green. This works fine except
> > empty cells i.e. containing no data, go green.
> > 
> > Has any one got any suggestions?
> > 
> > Best wishes
> > 
> > Nick
> 
> -- 
> 
> Dave Peterson
> 
0
NickHorn (2)
2/25/2007 5:15:05 PM
Reply:

Similar Artilces:

Manual or Formula entry in cell.
Hello all, I have a column in a spreadsheet called weekly costs. Not the majority of the weekly costs will be manual entries. However 25% need to be picked up from another spreadsheet. I could add an addtional colum say A and the have in a formula in Column B that picks up from col A or does a vlookup. But I feel that having 2 columns may cause confusion. What I am ideally looking for is to go into a cell and have a dropdown list that says manual or formula, clicking manual allows a manual entry, whereas clicking formula uses the value from the vlookup formula. Any way that this can b...

Creating Format Conditions via Code
I'm able to successfully create format conditions via code, while I'm able to manipulate the format of the control (.enabled, .forecolor, .backcolor), is it possible to control wether or not the field is visible or locked. For i = 0 To Me.subfrmLoadListDetail.Controls.Count - 1 Set ctl = Me.subfrmLoadListDetail.Controls(i) With ctl If .Tag Like "*" & "loadListDetail" & "*" Then For j = 0 To .FormatConditions.Count - 1 Debug.Print j, .Name, .FormatConditions.Count, ..FormatCondi...

Entering data to non protected cells
If I protect the table I can still "stop" at any cell and get the message if trying to enter data to protected area. My Q here is. can you avoid this being able to click on any cell and instead just jump to a non protected cell and to the next and next? Thanks Kalevi Hi, Use the "Tab" key to jump directly to unlocked cells. Your worksheet will have to be in protected mode for this to work. HTH Ron "Kalevi" wrote: > If I protect the table I can still "stop" at any cell and get the message if > trying to enter data to protected area. My ...

find all cells that match and use in an index/vlookup
I have a LARGE set of data... I parse it to a pivot. red blue Chair living room .1 .2 dining room .6 .3 bedroom .3 .4 sofa living room .9 .2 dining room .01 0 bedroom .2 .5 etc... (4 colums, multiple rows) I need to be able to: Find what room a soffa is most likely to be in, and what that chance is, and use that data in another worksheet where there will just be a list of furniture, in room, with max chance. I can seem to figure how to find the range of rows that have chairs (there are not always the same amou...

Possible Conditional Formatting Workaroud?
I have conditional formatting already set to shade a row differently for 3 conditions. But I also need to have a date cell's value change to red if it's manually changed. I'm wondering if there's a way to use Excel's custom number format feature to do this? Here's the layout: G - Submission Date (manually entered) H - Hidden Workdays field to determine a 3 day Due Date date based on G I - Displayed Due Date field that equals value of H J - Hidden # Days Turnaround Field in General format (currently equals I - G but can change that to H-G if I need to do that t...

"Blank" cells
Hi, I've got a table in Excel where I want to do this: suppose we're in line E; I want to fill all the cells, from E2 to E10, for example, with blank values if E11 < 0; all the referred cells - E2 to E10 - contain formulas. Can I do this? Kind regards, Kepler:confused: -- Kepler ------------------------------------------------------------------------ Kepler's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29328 View this thread: http://www.excelforum.com/showthread.php?threadid=490443 Not being able to see the existing formulas in E2:E10 kinda ...

CUSTOM TEXT FORMATTING???
My cell has 8 mixed characters of numbers and letters. I need to have a hyphen "-" after the first 4 characters in a column. Can I make a custom text format so no matter what I enter a hyphen is automatically inserted? How do I do that? JLC Hi this could only be done with VBA, using an event procedure (as you have text and numbers in your cell). e.g. put the following code in your worksheet module (not in a standard module): Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100"...

Arrow keys not moving 1 cell at a time
User has a spreadsheet where the arrow keys are doing a 'page down rather than moving 1 cell at a time. Not sure how this got set, or ho to get it back. Thanks for your help -- Message posted from http://www.ExcelForum.com slivi, check to see if scroll lock is on, on the key board -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "slivi >" <<slivi.11vnhp@e...

Highlighting selected cells/rows/columns
Try adjusting the contrast on your computer monitor. Mine are little buttons on the lower right just below the screen. My contrast was turned up on high, when it was lowered, I could see the gray shading on selected rows/columns/cells. ...

Format cells #5
I have Excel 2000, Win ME. Recently the pulldown command to format cells does not always respond - ie, no box appears, so I can't apply borders or control number format. The shortcut approach doesn't work either. I've tried Help..Detect and Repair but the fault remains. Any suggestions welcome. ...

onditional Formating
If I want to conditionally format a whole row, depending on the contents of just one cell in that row, can this be done? So, let's say I have the range A1:M100 I want each individual row (A to M) to be conditionally formatted if the value in the M cell of that row is <0.95, or >1.05 (2 different conditions & formats) Is this possible? Ideally without VBA, as I'm wanting to pass the technique onto staff whose Excel knowledge, is, shall we say, limited. Amy advice greatly appreciated. Jay Select all of the rows 1:100, goto CF, change Condition 1 To Formula Is, and a...

Using wide and regular format monitors
Hello All My job just gave me new Lenovo T400 laptop running XP. (No Windows 7 yet for us) It has a 14 inch wide format screen. I'm attaching the computer to a traditional 20 inch screen. The wide display on the laptop 1280 x 800 is squished and off the screen on the traditional montior. When I open display properties, it shows both montiors as Monitor 1. I'd like to have the traditional monitor work property without having to resize the display resolution every time I connect to it. (I do NOT want to use one monitor as an extension of my desktop). Is there a way ...

How do I print formula figures and calculated amount in a cell?
In order to pass a file monitoring on income eligibility, I need to be able to show the calculations in a paper copy of a spreadsheet, not just the final amount. For example: If someone makes 3000 a month, the calculation would be =3000*12. It is calculated using a cell calculation formula (ie. =G4*12 I need it to print 3000*12=36000. Does anyone have any ideas on how/if this is possible? Try this: =G4&"*12="&(G4*12) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "SLM" wrote: > In order to pass a file monitoring on income eligibility, I...

Last Cell of Worksheet
The shortcut CTRL+END is very handy for moving to the last cell on the worksheet, in the bottom-most used row of the rightmost used column. This shortcut works until I delete rows at the end of the worksheet. After I delete a few rows, the CTRL+END shortcut takes me to the last cell that used to hold data, not the last cell that contains data. Is there a shortcut that moves to the last cell on the worksheet that contains data? If not, is there a way to change what Excel thinks is the last cell so that the CTRL+END shortcut takes me to the last cell that contains data? Thanks in adva...

Ecel Cell Format Oddity
I have a problem with excel that may be a result of my obliviousness, or may be an actual problem. In short, when I enter a date, microsoft auto formats it as a formula, adding the equal sign in front as so =02/09/04. It does this even though the cell is formatted as a Date cell. This happened after everything was working well for the first 100 rows or so. I tried changing the formating back and forth, but the problem remains, and in fact if I go back to a cell which worked correctly in the past, it now also has this problem. Previous sheets still work fine however. I did figure out t...

AMVERAGIF Condition
I am having trouble adding an AND statement to the condition portion of the AVERAGEIF statement. Is there a trick to doing this? "And" will return true/false, try using IF(AND(-------)) which will return a value as the criteria. Regards Steve "rwtrader" <rwtrader@discussions.microsoft.com> wrote in message news:FD0D4875-8F7C-48C4-9AA5-1E3964E0A171@microsoft.com... >I am having trouble adding an AND statement to the condition portion of the > AVERAGEIF statement. Is there a trick to doing this? Don't know the new function AVERAGEIF, ...

Complicated conditional formatting problem [Excel 2007]
Hello! I have a cell with the following formula: =(AVERAGE(Table4[Column2]))&"/"&AVERAGE(Table4[Column3]) It displays for example: 145/80 Now, I`d like to color the cell red if any of the following is true: a) First value(here: 145) is not between 90 and 140 b) Second value (here:80) is not between 50 and 90 Any ideas how to do this? Greetings :) In the CF dialogue box you need to select Formula Is and then use this formula: =3DOR(--LEFT(A1,FIND("/",A1)-1)<90,--LEFT(A1,FIND("/",A1)-1)>140,-- RIGHT(A1,LEN(A1)-FIND("/",A1))<50,...

Task Duration Changes on SAVE & OPEN in XML format
Hi, We have discovered a strange problem in MSProject after installing SP2 The task duration changes when MPP file is saved as XML and XML file is reopened. This is causing a havoc. There is nothing specific about the file. File with even one task shows the problem. Any help possible. Can I upload the file somehwere? Hi, I'm running Project 2007 SP2. I created 7 tasks with durations from 1d to 7d and linked them. Saved to xml. Closed. Create new project from xml file and finish date of 7th task was same and all durations same. What's the minimum you have to...

How do I display images from an XML doc that are in this format>>
The xml doc has pictures but in the format of a very long character string. I'm not sure what this format is called so I do not know what type of converter I should be looking for. May have something to do with base64. I want to read it from the xml doc and display it in a windows form pictureBox. I'm programming in c#. example(it actually has a lot more characters than this) <Picture>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQAB2gAAAAAAAAAAACgKQAAQk2YKQAAAAAAAFYAAAAoAAAAgAAAABAAQAAAAAAAAAAACICwAAiAsAAAgAAAAIAAAAYKQAAAAAAAFYAAAAoAAAAgAAAABAAQAAA</Pi...

Exact Copy of Linked Cells in Excel
I have a Main information Sheet that links to corresponding othe sheets. When I type into the main cell, it will link the letter that type, but bold portions and fill color does not link. How can I hav an exact copy of my main cell? (I used Paste Special-Paste Link t link the cell) I attached an example of what it is doing. "Main Sheet" is what I wan my linked sheet to look like. "Linked Sheet" depicts what actuall happens with the link. Thanks, Jo -- Message posted from http://www.ExcelForum.com Hi formulas (that is your linked cell) can only return values. Ther...

XML Serializer
I have a class I am serializing, and need the resultant XML to skip/omit classes that are not initialized, or their member variables have not been set. Is this possible? Say for the following code, sometimes the Person may not have given us one, both, or either of their addresses. The serialize function will still put in a "<BillingAddress />" tag indicating no data. I want it to completely omit the tag if the class is empty, similar to the way it omits any tag for blank STRING members, such as if there is no SSN given. VB.NET SAMPLE CODE: ========================== Public C...

Safest image format to use with Word?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Well... IS there a safe image file format to use with Word? (jpeg vs tiff vs png vs...) <br><br>Until now, all of my Word documents have been lean, clean (styled) text only. But recently I've realized that it would be helpful to include images in my files. <br><br>Am I asking for trouble? Am I asking for less trouble if I... <br><br>- Insert them all as in-line graphics? <br> - &quot;Size&quot; the images in another application before inserting them so that ...

conditional formatting 02-19-10
Hi, Windows XP fully updated Excel 2002 sp3 is there a way to make a cell's background colour change depending upon the contents for ANOTHER cell. E.G. if cell A1 contains the word 'done' I would like the background colour of B1 to be yellow. Conditional formatting only seems to work on the cell in question not on another cell. Thanks -- Nospam This question appears as unanswered in my OE, so excuse me if this is an extra, unneeded reply. Select B1, then use CFR with the Custom option, with a formula that returns TRUE or FALSE IF A1 will j...

Interactive XY charts where data are in different number formats
I posted this question earlier but, in spite of Tushar Mehta's valiant efforts, I was unable to resolve my problem. My data are organized differently than in the examples cited on his web page and my knowledge of the OFFSET and COUNTA functions simply proved to be too spotty. So, if I may burden you again: I have an array of data organized as follows: Response 1 2 3 4 5 Data in cells A4:F19: Probability 80% 70% 65% 75% 85% Number of Customers 115 185 100 160 190 Selling price $2.15 $1.85 $2.00 $2.30 $2....

referencing cells in relation to named Cells
Not sure if this is possible but have named cells a1:a8 named as test01 what i want to do is in formulas be able to use =countif(test01+ 4 colomns),"Test") eg want to be able to use say e1:e8 in a formula but not have to use references each time have got a lot of formulas that use this range ?1:?8 and want to be able to simplify the way the formulas work can anyone help with this PS using Excel 2003 Not sure if Iam going about this the right way but any solution would be great Thanks Damien You can try something like this: =COUNTIF(INDEX(A1:E8,,5),"Test") Where...