 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2006Conditional formatting
Hi I am trying to create a holiday planner for the GP practice where I work, and have have inserted a number for available appointments a GP has in cell B1, but when they go on holiday I insert a H in cell A1 and it turns yellow, and in cell B1 I want it to change to overide the number of appointments by placing a 0 and also turning it yellow, I can get the colour to change but not the number, I have used formula is: =IF(A1="H",0) but it is not working, it works as a basic if function but not in conditional formatting, please help. Thank You Pam As this is conditional formatting, you don't need the IF bit. Select B1, then Format | Conditional Formatting and choose Formula Is rather than Cell Value is. Then enter this formula: =(A1="h") then click on Format, Patterns tab and choose yellow as the background colour and OK your way out of it. Excel often puts extra quotes in the formula, so if the background colour does not change when you have H in A1, then check the formula again and edit if necessary - it should be exactly as shown above. Hope this helps. Pete I should have pointed out that it is case-insensitive, so you could just as well have: =(A1="H") and both will work if you have H or h in A1. Pete Pete, .. and the question was .. ?? <g> I'm reading this thread from MS Office Online, where I see nothing except the OP's subject line: Conditional formatting. The OP is also "unknown", but of course <g>  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- I see he first two in the thread as replies by Pete, wonder if it is an old thread and even more than two months old so we don't even see it in a newsgroup. Don't know if that is the problem, but Google doesn't even warn someone if the thread is old. With such a common subject -- chances are Google has overlapped many threads and you won't really be able to tell mcuh. --- HTH, David McRitchie, Microsoft MVP - Excel [ My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm > Pete, > .. and the question was .. ?? <g> > I'm reading this thread from MS Office Online, where I see nothing except > the OP's subject line: Conditional formatting. The OP is also "unknown", but > of course <g> > I see the first two in the thread as replies by Pete, wonder if it is > an old thread and even more than two months old so we don't even > see it in a newsgroup. Don't know if that is the problem, but Google > doesn't even warn someone if the thread is old. With such a common > subject -- chances are Google has overlapped many threads and you > won't really be able to tell mcuh. Thanks, David. With your hint, I found the "complete" thread in Google, and it's with the OP intact (it's a new thread <g>) .. ---- OP's query -------- From: stephen.leona...@ntlworld.com Newsgroups: microsoft.public.excel.newusers Subject: Conditional formatting Date: 31 May 2006 05:05:26 -0700 I am trying to create a holiday planner for the GP practice where I work, and have have inserted a number for available appointments a GP has in cell B1, but when they go on holiday I insert a H in cell A1 and it turns yellow, and in cell B1 I want it to change to overide the number of appointments by placing a 0 and also turning it yellow, I can get the colour to change but not the number, I have used formula is: =IF(A1="H",0) but it is not working, it works as a basic if function but not in conditional formatting, please help. --------------- SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Original message appears okay in Google, and it was posted through Google Groups by Stephen Leona and not an old message either. Pete's replies were also both posted via Google Max's reply was posted using Microsoft CDO My reply was posted using OE to direct to Microsoft newsserver, and I can't see the originl message from Stephen at all in OE connected directly to Microsoft servers. My second reply (this post) was posted using Google Groups. So there was no old post involved, and no intertwined threads shown in Google either, just another one of those mysteries. The entire thread as seen in Google can be see at: http://groups.google.com/groups?threadm=1149077126.133240.149290%40i39g2000cwa.g ooglegroups.com As I see the question, you could select columns A:B with cell A1 as the active cell then use Formula is =$A1="H", if fact if you wanted the entire row to turn yellow you could select all cells on the sheet and as long as the active cell is on row 1, use the same formulas, and the entire row would turn yellow based on the value in the A column. Conditional Formatting http://www.mvps.org/dmcritchie/excel/condfmt.htm HTH, David McRitchie, Microsoft MVP, Excel > I see he first two in the thread as replies by Pete, wonder if it is Replying to Max's second reply his again posted via Microsoft CDO, and while I was waiting for my second message to appear (this was also posted via Google Groups), I'm going to have to fix my friendly name in Google Gorups for future use. Remains a mystery why some postings are not seen by everyone. -- David McRitchie dmcritchie_xl...@verizon.net wrote: > Original message appears okay in Google, and it was posted through Google Groups is not displaying sub threading correctly, this reply is from OE to Pete's first reply. So this post should be indented under 2nd reply (1st post that I see in OE) and should appear at the bottom. The really bizarre thing is that OE is also forced to show the same appearance order and indentation. The numbers are actually in chronological order you will only see the numbers in Google. Conditional formatting as seen in Google Groups when this was posted 1 stephen.leona...@ntlworld.com May 31 [Mozilla] 12:05:31 GMT 2 Pete_UK May 31 (reply to #1) [Mozilla] 3 Pete_UK May 31 (reply to #2) [Mozilla] 4 Max May 31 (reply to #3) 5 David McRitchie May 31 (reply to #4) 6 Max Jun 1 (reply to #5) 7 dmcritchie_xl...@verizon.net Jun 1 (reply to #5) 8 dmcritchie_xl...@verizon.net Jun 1 (reply to #6) 9 David McRitchie (reply to #2, as a test) think it will look like this Don't know about the numbers in front but indentations and order should appear as 6 Max Jun 1 (reply to #5) 8 dmcritchie_xl...@verizon.net Jun 1 (reply to #6) 7 dmcritchie_xl...@verizon.net Jun 1 (reply to #5) 9 David McRitchie (reply to #2, as a test) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm Signature--- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > As this is conditional formatting, you don't need the IF bit. Select > B1, then Format | Conditional Formatting and choose Formula Is rather [quoted text clipped - 11 lines] > > Pete I'm sorry I caused such confusion by responding to a thread !! As David points out, I used Google Groups, and this is where I saw the OP - I thought it was strange that no-one had responded to such a fairly straightforward problem, although the post was half a day old. I've also found in the past that some of my posts were not reproduced in some other groups, notably Excel Forum - I haven't a clue why (nor did I understand how they got there in the first place). Pete > Google Groups is not displaying sub threading correctly, this > reply is from OE to Pete's first reply. So this post should be [quoted text clipped - 26 lines] > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm Pete I dont think I have explained my problem very well, basically I've entered the number 33 in cell B1 and want it to change automatically to a 0 when entering a letter H in A1,the replies I have recieved are telling me to enter a IF fuction in cell B1 but by doing this my number 33 goes and I need these numbers for a total. Thanks Pam I am somewhat reluctant to post a response to this question in case it has been answered and I just cannot see it but right-click on the sheet tab and select View Code then paste this cod into the window that will open up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'If entry not in column A then do nothing If Target.Column <> 1 Then Exit Sub 'If deleting the letter in column A then do nothing If Target.Value = "" Then Exit Sub Cells(Target.Row, 2).ClearContents End Sub SignatureHTH Sandy In Perth, the ancient capital of Scotland sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Pete > [quoted text clipped - 7 lines] > > Pam > Pete > [quoted text clipped - 7 lines] > > Pam I thought your problem was about Conditional Formatting, but thanks for the clarification. What you are asking now, though, seems a bit like having your cake and eating it - you want the cell to show 0, but you want to retain the value of 33 that you have put in there! You could apply conditional formatting to the cell to set the foreground colour to the same as the background colour (yellow?) if A1 = "H", so that it would appear to be empty, but the value would be as it was - hence your totals would still apply. Hope this helps. Pete |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.