blank cell #3

How do I usse conditional formatting to tell all the cells in a particular 
column to change one color if there is something in a cell, and another if 
it is blank?

The column in question is the "Date Closed" column and I want the blank 
cells to stand out from the rest.
Thanks
Ross


0
ssor (40)
9/17/2005 6:20:19 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
567 Views

Similar Articles

[PageSpeed] 22

Select the column (I used E).

And with E1 the active cell...

Format|conditional formatting
formula is:  =E1=""
choose a nice format



Ross wrote:
> 
> How do I usse conditional formatting to tell all the cells in a particular
> column to change one color if there is something in a cell, and another if
> it is blank?
> 
> The column in question is the "Date Closed" column and I want the blank
> cells to stand out from the rest.
> Thanks
> Ross

-- 

Dave Peterson
0
petersod (12005)
9/17/2005 6:37:55 PM
I tried that and it didn't work.  Probably did it wrong.  I selected the 
column -- in my case J, with J1 active cell (how do I make it the active 
cell? by clicking before I select the column?).  then went to conditional 
formatting, selecte formula from dropdown and pasted in your formula and 
changed it to suit my column   It now reads ="j1="  I set the format so the 
cell background would become red, but when I clicked ok, no change occured.
Ross
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:432C6283.A0E92A9D@verizonXSPAM.net...
> Select the column (I used E).
>
> And with E1 the active cell...
>
> Format|conditional formatting
> formula is:  =E1=""
> choose a nice format
>
>
>
> Ross wrote:
>>
>> How do I usse conditional formatting to tell all the cells in a 
>> particular
>> column to change one color if there is something in a cell, and another 
>> if
>> it is blank?
>>
>> The column in question is the "Date Closed" column and I want the blank
>> cells to stand out from the rest.
>> Thanks
>> Ross
>
> -- 
>
> Dave Peterson 


0
ssor (40)
9/17/2005 7:08:36 PM
Hi!

>with J1 active cell (how do I make it the active cell? by clicking before I 
>select the column?)

When you select a range of cells, say, J1:J10, the first cell in the range 
has no "shaded" color while the rest are "shaded" a blueish gray. The cell 
that is not shaded (the first cell in the range) is the active cell. You 
could also select the range from J10:J1 and J10 would be the active cell. As 
a general rule it's best to select ranges from top to bottom or left to 
right.

>It now reads ="j1="

Change that to:

=J1=""

Biff

"Ross" <ssor@twcny.rr.com> wrote in message 
news:UQZWe.79746$EX.50436@twister.nyroc.rr.com...
>I tried that and it didn't work.  Probably did it wrong.  I selected the 
>column -- in my case J, with J1 active cell (how do I make it the active 
>cell? by clicking before I select the column?).  then went to conditional 
>formatting, selecte formula from dropdown and pasted in your formula and 
>changed it to suit my column   It now reads ="j1="  I set the format so the 
>cell background would become red, but when I clicked ok, no change occured.
> Ross
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
> news:432C6283.A0E92A9D@verizonXSPAM.net...
>> Select the column (I used E).
>>
>> And with E1 the active cell...
>>
>> Format|conditional formatting
>> formula is:  =E1=""
>> choose a nice format
>>
>>
>>
>> Ross wrote:
>>>
>>> How do I usse conditional formatting to tell all the cells in a 
>>> particular
>>> column to change one color if there is something in a cell, and another 
>>> if
>>> it is blank?
>>>
>>> The column in question is the "Date Closed" column and I want the blank
>>> cells to stand out from the rest.
>>> Thanks
>>> Ross
>>
>> -- 
>>
>> Dave Peterson
>
> 


0
biffinpitt (3172)
9/17/2005 9:06:13 PM
And just to add to Biff's response...

If you select the column J by clicking on the column header with Row 1 visible,
then J1 should be the activecell.

(Or select J1 and hit ctrl-spacebar)

Biff wrote:
> 
> Hi!
> 
> >with J1 active cell (how do I make it the active cell? by clicking before I
> >select the column?)
> 
> When you select a range of cells, say, J1:J10, the first cell in the range
> has no "shaded" color while the rest are "shaded" a blueish gray. The cell
> that is not shaded (the first cell in the range) is the active cell. You
> could also select the range from J10:J1 and J10 would be the active cell. As
> a general rule it's best to select ranges from top to bottom or left to
> right.
> 
> >It now reads ="j1="
> 
> Change that to:
> 
> =J1=""
> 
> Biff
> 
> "Ross" <ssor@twcny.rr.com> wrote in message
> news:UQZWe.79746$EX.50436@twister.nyroc.rr.com...
> >I tried that and it didn't work.  Probably did it wrong.  I selected the
> >column -- in my case J, with J1 active cell (how do I make it the active
> >cell? by clicking before I select the column?).  then went to conditional
> >formatting, selecte formula from dropdown and pasted in your formula and
> >changed it to suit my column   It now reads ="j1="  I set the format so the
> >cell background would become red, but when I clicked ok, no change occured.
> > Ross
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:432C6283.A0E92A9D@verizonXSPAM.net...
> >> Select the column (I used E).
> >>
> >> And with E1 the active cell...
> >>
> >> Format|conditional formatting
> >> formula is:  =E1=""
> >> choose a nice format
> >>
> >>
> >>
> >> Ross wrote:
> >>>
> >>> How do I usse conditional formatting to tell all the cells in a
> >>> particular
> >>> column to change one color if there is something in a cell, and another
> >>> if
> >>> it is blank?
> >>>
> >>> The column in question is the "Date Closed" column and I want the blank
> >>> cells to stand out from the rest.
> >>> Thanks
> >>> Ross
> >>
> >> --
> >>
> >> Dave Peterson
> >
> >

-- 

Dave Peterson
0
petersod (12005)
9/17/2005 10:39:49 PM
Reply:

Similar Artilces:

Copying cell contents to a list
First of all - apologies if this is in the wrong group. I wasn't sure if it should be newusers (which I am) or programming (which I might need). I have inherited a lottery syndicate at work after the previous organiser retired. He kept an Excel 97 spreadsheet with 8 columns - A (Date), B to G (Winning numbers) and H (Bonus number). This list has now grown to about 800 rows as each draw's numbers are added to the bottom of the list (I will be removing about 600). I have adapted the spreadsheet to automatically check our numbers against the winning numbers (using HLOOKUP) after I have ...

how do I apply more than 3 conditional formats in excel
I want to create a themal analysis problem whereby the results of the calculation are displayed in a grid of cells (representing the shape of the item). I then want to apply conditional formatting which colours the cells depending on the value. I want there to be 10 colours ranges. I can do this easily for four colours using standard conditional formatting but I want to use 10 colours, is there a way to expand the conditional formatting? Hi couple of options - there's a conditional formatting add-in available for download at www.xldynamic.com/source/xld.CFPlus.Download.html or you...

List Control #3
how to move one value from one LIST control to Another List control. -- farah ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------ If you mean dragging and dropping follow this link. There is sample showing how to implement dragging/dropping items between list controls. http://www.codeguru.com/forum/showthread.php?t=331244 "farah" wrote: > > how to move one value from one LIST control to Another List control. > > > > -- &g...

Unlinking tracked emails in CRM 3.0
I find that once an email is linked to/tracked by CRM (Customer, Client, Activity) it can't be unlinked! only deleted. For example; A client uses an old CRM generated email containing the tracking token to ask about a new problem. CRM automatically tracks the new email to the old CRM case and I can't untrack it to link it to a new case. Any information would be helpful... Hi, Check out "How to "untrack" Microsoft CRM Outlook e-mails" on following blog http://blog.sonomapartners.com/ -- PLEASE do click on Yes or No button if this post was helpful or not for ou...

Tab to specific cells
I am using excel 2002 and my boss 2003. I want to tab to specific cells in my work sheet. Can anyone help me get started? You could try the named range approach. Select your *second* cell for data entry and then hold down <Ctrl> and continue to select all the rest of the desired cells in order, ending with selecting your *first* cell *last*. While the cells are still selected, click in the name box and give this range a short name such as "Entry1". Now, when you're ready for data entry, simply click on the range name in the name box. The focus for the range is th...

Text to speech particular cell
hi ive been trying for a while now to get the text to speech function to work for a particular cell. For example, i have a set of numbers and a total in D15, what i would like is that if i change the numbers that contribute to that total, and the total in D15 changes then excel will alert me saying "Change in Total". ive tried and failed to get it notice the changed total and would really appreciate some help Thank You for any contributions Let's put the message in a cell, say D16. In a standard module insert the follow line: Public OldVar As Variant Insert the following...

Moving Exchange 5.5 #3
We currently have W2K3 domain with external 2-way trust with NT4 domain where Exchange 5.5 is located. I'd lik to move my Exchange to the new server W2K member of the W2K3 domain. During the change I'd like to change also the organization and site name. Does anyboby have any information on how can easily do this without any client reconfiguration? Thanks TagaR On Tue, 25 Jan 2005 09:31:06 -0800, "TagaR" <TagaR@discussions.microsoft.com> wrote: >We currently have W2K3 domain with external 2-way trust with NT4 domain where >Exchange 5.5 is located. I'd l...

can I find merged cells?
I'm trying to sort and get the message "merged cells must be the same size". How can I 'find' the merged cells? David, here is a macro by Dave Peterson that will do it Sub Found_Merged_Cells() 'macro looks for merged cells 'By Dave Peterson Dim myCell As Range Dim resp As Long For Each myCell In ActiveSheet.UsedRange.Cells If myCell.MergeCells Then If myCell.Address = myCell.MergeArea(1).Address Then resp = MsgBox(prompt:="found: " _ & myCell.MergeArea.Addre...

Place X in cell if criteria met`
Is there a formula to do this? If cell B2 = pencils Put an "X" in cell B7 If cell B2 = pens Put an "X" in cell B8 If cell B2 = erasers Put an "X" in cell B9 Thanks in advance in cells B7 put =if(B2="pencils","x","") in Cell B8 put =if(B2="pens","x","") In cell B9 put =if(B2="erasers","x","") "jhicsupt" wrote: > Is there a formula to do this? > > If cell B2 = pencils > Put an "X" in cell B7 > > If cell B2 = pens ...

Write formula for simple copy and paste to another cell
I have a worksheet that contains hundreds of columns. Each group of three are related to each other (Cols A-C, D-F, etc...). I have this formula that works well for columns A-C =IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B$1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=COLUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B$2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COLUMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B$2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A...

Send Email With Cell Conent as Part of the Subject
Hyperlink is able to send email with cell content in the Subject line. My Case =HYPERLINK("mailto:Collections_Admin?subject=Overdue Balance " & "_"& 'Sheet1'!G15,"Email To Collection Agency") Sheet1G15 has the data I want to show in the subject line. Is it possible to do this with Macros? I am using Lotus Notes for email. Thanks Igbert For lotus code see Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "igbe...

Label a button from a cell content
Hello, Using Excel 97, is their a way to aquire the label for a button from cell on a different worksheet? :confused: Thanks in advance, Sp -- spyrul ----------------------------------------------------------------------- spyrule's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2548 View this thread: http://www.excelforum.com/showthread.php?threadid=38935 ...

inserting texts in cell based on conditions
Hi, I would very much appreciate if someone could help me solving a problem, illustrated by the following example: Column A B C 1 1 "LB" 2 1 "DK" 3 4 1 1 "LB/DK" If there's a 1 in column A, the corresponding cell in column C should get the text "LB" inserted into it. If there's a 1 in column C, the corresponding cell in column C should get the text "DK" inserted into it. If both column A and B have ones in them, the corresponding cell should get the...

How do I Add and Subtract percentages in cells
Can anybody help I am trying to add and subtract percentages from cells deducting tax from wages etc. How do i go about it.As i am new to excel Thanks in advance. Ron Ronnie Check out this on-line tutorial for Excel. http://www.usd.edu/trio/tut/excel/index.html Specifically the "Basic Math" section. http://www.usd.edu/trio/tut/excel/13.html Gord Dibben Excel MVP On Sun, 1 Aug 2004 23:06:56 +0100, "Ronnie" <ronnie@broraweb.co.uk> wrote: >Can anybody help I am trying to add and subtract percentages from cells >deducting tax from wages etc. >How do i ...

I want to format a cell based on an adjacent cells value
I would like a cell automatically formatted in the same way a conditional format works - but the format to be based on the value of an adjacent cell. Any ideas as I cant work out how or if I can achieve this Cumbo, Use Conditional Formatting, but change cell valve is to formula is and use something like this, will change the cell if A1=25 =$A$1=25, so if you select B5 and put this in conditional formatting cell B5 will change when A1 = 25 -- 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 an...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Skip blank cells in diagrams
How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. You can include the function NA() in that field and the zero value for the data won't be displayed. "hlp" <hlp@discussions.microsoft.com> wrote in message news:4FF83D9F-F13E-4815-BDDE-26F44F2E6BE1@microsoft.com... > How do I exclude blank cells in diagrams. If I have an area of data and among > these data some is blank. How do I get excel to not display these data as '0...

Multiple IF statements in one cell
Excel 2003 on XP. I have a project control listing that has, in part of it, nine columns that will hold the dates that certain stages are met, i.e. a date will be entered as each stage is met and the following stages will be empty, until Stage 9 - Project Complete/Signed Off. Stages 1-9 each have a unique Stage Name/defintion. I would like a following cell in the row to automatically show the Stage Name for the latest date in the corresponding stage cell for that project. I have a nested IF statement that works but as the project list is to get very large I would like a neater way of doin...

Counting cells based on color
I am looking for a "counting" formula that will count cells in a rang based on their color, so that if I color a series of dates (to sho 'vacation) the number of cells (days) will be counted...to keep trac of vacation...thanks -- Message posted from http://www.ExcelForum.com There's no built-in functionality for that - it requires a UDF (user-defined function). You'll find several examples in the google archives: http://www.google.com/advanced_group_search? as_ugroup=*excel*&lr=&num=100&hl=en MVP Chip Pearson also has some code: http://www.cpearson.com...

Cell formats, and time difference
Hi, some simple questions which I hope someone can help with... I am using excel on a computer with "danish locale". This means that numbers are displayed default like 1234,56. How do I change this to a format using a decimal point (instead of a comma)? How do I find the difference between to times? I have cells which just have times (not dates), and I want to find the difference between two times in hours. For example cells with 08:00 and 09:30 should give a difference of 1.5 Excel help gives an example like =TEXT(B2-A2,"h"), but this gives an error... Thanks, Pet...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

minimum for casual cells but >0
I have big table with vendors, and I have to find the lowest price for each item, but prices are like: A B C D E F G H I etc. Item x 128 175 0 215 the problem is there are also columns with quantities which shouldn't be included I don't want to include columns e.g. B, D, F etc. AND find minimum price but >0. so I can't use: MIN(C1,E1,G1,I1...) because I'll get 0 as result. I'm looking for the simplest solution. Appreciate for any help. Ctrl/shift/ente...

AX for Retail. Is that RMS 3.0?
Hey, Does anyone know much about AX for Retail or has heard anything about it? I am using RMS 2.0.0126 now on Vista 32 bit but am wanting to upgrade to Windows 7 64 bit and would like to know if AX for Retail is better then RMS. Thanks, -- Steve Steve, Please drop an email to get the details. MV nj.tech@hotmail.com "Steve @ S&S Tire" wrote: > Hey, > > Does anyone know much about AX for Retail or has heard anything about it? I > am using RMS 2.0.0126 now on Vista 32 bit but am wanting to upgrade to > Windows 7 64 bit and would li...

Does Microsoft CRM 3.0 extend the Active Directory schema?
Q. Does Microsoft CRM 3.0 extend the Active Directory schema? No, the AD schema is not extended. It simply creates an add'l Organization Unit and some security groups under that OU. No changes that can't be simply undone... "Rifat Yavuz" <Rifat Yavuz@discussions.microsoft.com> wrote in message news:B5E9FBF3-D8B5-4735-AC87-494AA2B81599@microsoft.com... > Q. Does Microsoft CRM 3.0 extend the Active Directory schema? thanks Chris Rifat Yavuz "Chris Resch" wrote: > No, the AD schema is not extended. It simply creates an add'l Organization...