Conditional Formatting dependent on whether cell is populated.

I want to conditionally format cell G2 dependent upon whether there is a 
value in cell C2 AND G2

Therefore, 
if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber
if cell C2 has a value in it and cell G2 has a value then cell G2 color = 
Green

Any helpful hints or useful suggestions? 

Thanks

Tel
0
Utf
1/20/2010 2:59:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
957 Views

Similar Articles

[PageSpeed] 12

Conditional Format of G2,
condition 1:
=AND(COUNTA(C2)=1,ISBLANK(G2))
format Amber

condition 2:
=COUNTA(C2,G2)=2
format Green
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tel" wrote:

> I want to conditionally format cell G2 dependent upon whether there is a 
> value in cell C2 AND G2
> 
> Therefore, 
> if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber
> if cell C2 has a value in it and cell G2 has a value then cell G2 color = 
> Green
> 
> Any helpful hints or useful suggestions? 
> 
> Thanks
> 
> Tel
0
Utf
1/20/2010 3:04:03 PM
Depends what you mean by "a value".  Is the null string "" a value in your 
terms?
CF/ Formula Is/ =AND(C2<>"",G2="") for Amber [or 
=AND(NOT(ISBLANK(C2),ISBLANK(G2))]
Either modify the above for your Green condition, or =COUNTA(C2,G2)=2
--
David Biddulph

"Tel" <Tel@discussions.microsoft.com> wrote in message 
news:9198DD00-6564-4319-AE51-892CEDEA824B@microsoft.com...
>I want to conditionally format cell G2 dependent upon whether there is a
> value in cell C2 AND G2
>
> Therefore,
> if cell C2 has a value in it and cell G2 doesn't then cell G2 color = 
> Amber
> if cell C2 has a value in it and cell G2 has a value then cell G2 color =
> Green
>
> Any helpful hints or useful suggestions?
>
> Thanks
>
> Tel 


0
David
1/20/2010 3:16:58 PM
Excellent, thank you

Tel

"Luke M" wrote:

> Conditional Format of G2,
> condition 1:
> =AND(COUNTA(C2)=1,ISBLANK(G2))
> format Amber
> 
> condition 2:
> =COUNTA(C2,G2)=2
> format Green
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "Tel" wrote:
> 
> > I want to conditionally format cell G2 dependent upon whether there is a 
> > value in cell C2 AND G2
> > 
> > Therefore, 
> > if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber
> > if cell C2 has a value in it and cell G2 has a value then cell G2 color = 
> > Green
> > 
> > Any helpful hints or useful suggestions? 
> > 
> > Thanks
> > 
> > Tel
0
Utf
1/20/2010 3:25:01 PM
Reply:

Similar Artilces:

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

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

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

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Losing all formatting in Excel 2007 files
I have Office 2007 running on an XP desktop and a Windows 7 laptop. I work on an Excel .xlsx file on my desktop but when I open it on my laptop all of the formatting is lost. I have had this exact same problem except vista to xp. I wouldn't lose all of the formatting, but some words would would be on different lines. Other things I have noticed is that my Office 2007 on Vista doesn't always have the same fonts as on my Office 2007 XP. I think the formatting issue is more of a compatibility issue. ...

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

Conditional Formatting with Relative Reference.
Conditional Formatting with Relative Reference. Sample workbook: A B C D E F 1. 4.5 6 7.4 8.3 10 2 2. 1 2 3 4 3 2 3. 1 2 3 4 5 6 4. 6 5 4 3 2 1 Hi, How can Excel 2003 workbook be designed so that if a value of a cell is smaller or greater than the value of the cell, then color of the cell is auto formatted ? Formatting of the cell is as follows: Green if value is greater and Red if ...

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

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

Custom number format with colors
Hi, I would like to format my numbers this way: > 0 --> Green = 0 --> Black < 0 --> Red How can I do that? Thanks!! Go to Format->Conditional Formatting... set your specs and click the format button to format the text the way you want. You will do a seperate one for each color. Select the entire sheet first though (CTL-A) -- -John Please rate when your question is answered to help us and others know what is helpful. "andre.labonte@gmail.com" wrote: > Hi, > > I would like to format my numbers this way: > > > 0 --> Green > = ...

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

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

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

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

CSV formatted text file to Excel
Hi all, I am writing a small VC++ application of how to import the CSV formatted ..txt file to excel. I am facing problem while parsing the text file. "TicketNo","CarNo","PersonAge" 12534 , 763534 , 23 12345 , 624333, 24 The problem is in MFC there is a SetValue2(CoeVariant:column data) method in which if i will pass an array(12534) then it will be imported to excel.For example IfI will search for the "employee number" field in text file then the values passed to SetValue2()should be 12543 and 12345.But Using C++ I cannot do so as I...

Conditional Formatting
Hi All, I need help on conditional Formatting. I have a column of data with future DATE, such as 2 Jan 09, 4 Des 09, etc I would like to assign automatically different colors to those data that match these condition: If (TODAY's date < Data's date) And more than 30 days, COLOUR is Orange If (TODAY's date < Data's date) And more than 60 days, COLOUR is Yellow If (TODAY's date < Data's date) And more than 90 days, COLOUR is Green If (TODAY's date >= Data's date) And more than 30 days, COLOUR is Red I would like to gave it AUTO...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

Different background color depending on x-axis value
Hi! Is there any way to have the background in a chart have differen colors depending on x-axis values? Like x-axis value 1-10 => blue background x-axis value 10-20 => red background Thank -- RealRave ----------------------------------------------------------------------- RealRaven's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3705 View this thread: http://www.excelforum.com/showthread.php?threadid=56778 Here are a few of the ways you can get custom background fills for your charts" http://PeltierTech.com/Excel/Charts/ChartIndex.html#Backgro...

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Conditional Formating Furmula
I have to enter a 14 digit licence number in a field this is a mix of numbers and leters. can some one please give me a formula that i can use in conditional formating tol show if there are to few or to many digits. (Using excel 2003) Thaks in advance. CF/ Formula is/ =LEN(A1)<14 Format as desired for too few CF/ Formula is/ =LEN(A1)>14 Format as desired for too many Rather than using CF you could, of course, use Data validation to require LEN(A1) to be 14. -- David Biddulph "jockj215" <jockj215@discussions.microsoft.com> wrote in message news:A24...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...