Advanced Conditional Formatting and Changing Cell Colours

I have three basic (for programmers) conditional formatting problems
to solve in Excel for which I require some help. These are the last 3
conditions I need to complete for the worksheet so any help will be
really appreciated. They are as follows:

1.
If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell
Z2


2.
If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I
need to change the colour of the cell containing "Z" (V2, W2 or X2).
If changing the colour of only the single cell containing "Z" cannot
be done, I guess changing the colour of the whole row would be okay.


3.
If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the
colour of the row


Thanks to the google group community in advance!

0
2/9/2007 2:40:46 AM
excel 39879 articles. 2 followers. Follow

5 Replies
664 Views

Similar Articles

[PageSpeed] 25

Might be an idea if you read the replies to your last two postings on the same
subject.


Gord Dibben  MS Excel MVP

On 8 Feb 2007 18:40:46 -0800, gregglazar@gmail.com wrote:

>I have three basic (for programmers) conditional formatting problems
>to solve in Excel for which I require some help. These are the last 3
>conditions I need to complete for the worksheet so any help will be
>really appreciated. They are as follows:
>
>1.
>If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell
>Z2
>
>
>2.
>If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I
>need to change the colour of the cell containing "Z" (V2, W2 or X2).
>If changing the colour of only the single cell containing "Z" cannot
>be done, I guess changing the colour of the whole row would be okay.
>
>
>3.
>If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the
>colour of the row
>
>
>Thanks to the google group community in advance!

0
Gord
2/9/2007 2:58:21 AM
On Feb 9, 1:58 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Might be an idea if you read the replies to your last two postings on the same
> subject.
>
> Gord Dibben  MS Excel MVP
>
> On 8 Feb 2007 18:40:46 -0800, greggla...@gmail.com wrote:
>
>
>
> >I have three basic (for programmers) conditional formatting problems
> >to solve in Excel for which I require some help. These are the last 3
> >conditions I need to complete for the worksheet so any help will be
> >really appreciated. They are as follows:
>
> >1.
> >If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell
> >Z2
>
> >2.
> >If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I
> >need to change the colour of the cell containing "Z" (V2, W2 or X2).
> >If changing the colour of only the single cell containing "Z" cannot
> >be done, I guess changing the colour of the whole row would be okay.
>
> >3.
> >If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the
> >colour of the row
>
> >Thanks to the google group community in advance!- Hide quoted text -
>
> - Show quoted text -

Unfortunately the info they contain is not correct. I am posting them
again in attempt to get a response that I can use, but you can be rude
and nasty about it if you like. I guess no one can stop you

0
2/9/2007 3:07:25 AM
If the responses to your other posts did not help, stick(reply)to the threads
you found them in and explain what did or did not work rather than just posting
again in another news group.

That's how things are done in these groups in order to maximize the assistance
you get.

If you consider this to be a rude and nasty reply then I do not apologize.


Gord

On 8 Feb 2007 19:07:25 -0800, gregglazar@gmail.com wrote:

>On Feb 9, 1:58 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>> Might be an idea if you read the replies to your last two postings on the same
>> subject.
>>
>> Gord Dibben  MS Excel MVP
>>
>> On 8 Feb 2007 18:40:46 -0800, greggla...@gmail.com wrote:
>>
>>
>>
>> >I have three basic (for programmers) conditional formatting problems
>> >to solve in Excel for which I require some help. These are the last 3
>> >conditions I need to complete for the worksheet so any help will be
>> >really appreciated. They are as follows:
>>
>> >1.
>> >If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell
>> >Z2
>>
>> >2.
>> >If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I
>> >need to change the colour of the cell containing "Z" (V2, W2 or X2).
>> >If changing the colour of only the single cell containing "Z" cannot
>> >be done, I guess changing the colour of the whole row would be okay.
>>
>> >3.
>> >If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the
>> >colour of the row
>>
>> >Thanks to the google group community in advance!- Hide quoted text -
>>
>> - Show quoted text -
>
>Unfortunately the info they contain is not correct. I am posting them
>again in attempt to get a response that I can use, but you can be rude
>and nasty about it if you like. I guess no one can stop you

0
Gord
2/9/2007 3:17:21 AM
On Feb 9, 2:17 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> If the responses to your other posts did not help, stick(reply)to the threads
> you found them in and explain what did or did not work rather than just posting
> again in another news group.
>
> That's how things are done in these groups in order to maximize the assistance
> you get.
>
> If you consider this to be a rude and nasty reply then I do not apologize.
>
> Gord
>
> On 8 Feb 2007 19:07:25 -0800, greggla...@gmail.com wrote:
>
>
>
> >On Feb 9, 1:58 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> >> Might be an idea if you read the replies to your last two postings on the same
> >> subject.
>
> >> Gord Dibben  MS Excel MVP
>
> >> On 8 Feb 2007 18:40:46 -0800, greggla...@gmail.com wrote:
>
> >> >I have three basic (for programmers) conditional formatting problems
> >> >to solve in Excel for which I require some help. These are the last 3
> >> >conditions I need to complete for the worksheet so any help will be
> >> >really appreciated. They are as follows:
>
> >> >1.
> >> >If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell
> >> >Z2
>
> >> >2.
> >> >If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I
> >> >need to change the colour of the cell containing "Z" (V2, W2 or X2).
> >> >If changing the colour of only the single cell containing "Z" cannot
> >> >be done, I guess changing the colour of the whole row would be okay.
>
> >> >3.
> >> >If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the
> >> >colour of the row
>
> >> >Thanks to the google group community in advance!- Hide quoted text -
>
> >> - Show quoted text -
>
> >Unfortunately the info they contain is not correct. I am posting them
> >again in attempt to get a response that I can use, but you can be rude
> >and nasty about it if you like. I guess no one can stop you- Hide quoted text -
>
> - Show quoted text -

This wasn't a rude and nasty response at all. Your first one was, and
don't worry I certainly wasn't expecting an apology from your decent
self

0
2/9/2007 3:21:49 AM
Just as flies are attracted to sugar, and not vinegar, helpful suggestions 
are attracted by congenial requests and responses, not ones that are 
discourteous and/or impertinent.

Such threads are just ignored and left unanswered.
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

<gregglazar@gmail.com> wrote in message 
news:1170991309.286684.42770@h3g2000cwc.googlegroups.com...
On Feb 9, 2:17 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> If the responses to your other posts did not help, stick(reply)to the 
> threads
> you found them in and explain what did or did not work rather than just 
> posting
> again in another news group.
>
> That's how things are done in these groups in order to maximize the 
> assistance
> you get.
>
> If you consider this to be a rude and nasty reply then I do not apologize.
>
> Gord
>
> On 8 Feb 2007 19:07:25 -0800, greggla...@gmail.com wrote:
>
>
>
> >On Feb 9, 1:58 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> >> Might be an idea if you read the replies to your last two postings on 
> >> the same
> >> subject.
>
> >> Gord Dibben  MS Excel MVP
>
> >> On 8 Feb 2007 18:40:46 -0800, greggla...@gmail.com wrote:
>
> >> >I have three basic (for programmers) conditional formatting problems
> >> >to solve in Excel for which I require some help. These are the last 3
> >> >conditions I need to complete for the worksheet so any help will be
> >> >really appreciated. They are as follows:
>
> >> >1.
> >> >If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell
> >> >Z2
>
> >> >2.
> >> >If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I
> >> >need to change the colour of the cell containing "Z" (V2, W2 or X2).
> >> >If changing the colour of only the single cell containing "Z" cannot
> >> >be done, I guess changing the colour of the whole row would be okay.
>
> >> >3.
> >> >If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the
> >> >colour of the row
>
> >> >Thanks to the google group community in advance!- Hide quoted text -
>
> >> - Show quoted text -
>
> >Unfortunately the info they contain is not correct. I am posting them
> >again in attempt to get a response that I can use, but you can be rude
> >and nasty about it if you like. I guess no one can stop you- Hide quoted 
> >text -
>
> - Show quoted text -

This wasn't a rude and nasty response at all. Your first one was, and
don't worry I certainly wasn't expecting an apology from your decent
self


0
ragdyer1 (4060)
2/9/2007 3:25:03 PM
Reply:

Similar Artilces:

Help with Conditional SUMIF Formula
Hi, I would like your help in creating a conditional SUMIF formula. I want to sum column B if column A is between 1/1/10 and today. Below is an example of the data. Any suggestions? Today 4/17/2010 Desired Result = $3,183 Col A Col B 1/1/10 $804 2/1/10 $799 3/1/10 $793 4/1/10 $788 5/1/10 $783 6/1/10 $777 7/1/10 $772 8/1/10 $766 9/1/10 $761 10/1/10 $755 11/1/10 $750 12/1/10 $744 One way... =SUMIF(A2:A13,">="&DATE(2010,1,1),B2:B13)-SUMIF(A2:A13,">"&TODAY(),B2:B13) -- Biff Microsoft Excel MVP "SanCarlosCyclist"...

3 colour in conditional format
i was made my mp3 list in excel.....currently i have successfully used conditional format based on coloumn criteria (i`m using a combo box just 'yes' and 'no')....my first conditional format is like this (=$H5:H407= "NO") and second one (=$IT5:IT407= "NO").... but i don`t know how to make a third one...i mean to combine th formula.....when these two coloumns is "NO".......anyone can help.... -- Message posted from http://www.ExcelForum.com Condition 1 should check both columns: =AND($H5="No","$I5="No") Condition 2 can...

Can I change default workplace "All" filter to "Appointments"??
Hi all, We heavily use the Appointments section of the CRM Workplace, and have defined a number of important Appointment views (Today's Appointments, Tomorrow's Appointments, To Be Scheduled Appointments, Waiting for Callback), etc. (We heaviy customized Appointments to add a status value, and links to our custom entities. Very useful!) The views all work fine, except they don't show up in the main set of activity views. They DO show up when the Type = "Appointment", so using these views require the user to: a) Click on Workplace b) Change the Type from All to Appoin...

Automatic formating of message display
Hallo, my would like to use automatic formating of message views. When he define cindition for his "rule", he would like to show all our domain users in his rule, so he would like to use rule like "when field by contains ourdomain.com" . We have tested it and it seems thah this rules in field send-by use only senders name (in exchange we dont see user's email, but only his name) my question is : how to build rule to apply to email with some string in sender's email even sender is our exchange user? hope I descrived it enough thanks for any help Polp ...

Exchange Hosting and PW Changes
Hello, We are hosting an Exchange 2k3 server that customers access via a VPN connection using the Outlook 2003 client. The Exchange server is it's own AD server and is separate from the end-users AD environments. We have implemented password expirations, but due to the pass-through authentication that appears to be used in a VPN/hosted configuration, the "14 day reminder" messages are never displayed.. How do others handle this? I'm planning to write a daily vbScript to send email notifications, but is there something better? Thanks, Troy ...

Format Conditioning/Rank
xl2000 I want to add a format condition to column A It would place a border around the cell in range a7:a31 The criteria being which corresponding cell in c7:c31 has the lowest value. The values are all positive numbers to 2 decimal places I have tried in a7 the following = c7 = rank(c7, c7:c31, 2) It does not reject it but does not work either. Any help would be appreciated. Ta, Martin Hi Martin, If I understand you correctly. Use this formula in your conditional formatting: =A7=MIN(C7:C31) If the value in A7 = the lowest value in the range C7:C31 then the border format will be applie...

is it possible for a excel document to copy cells to another excel
I have created a timesheet in Microsoft Excel 2003 I have now created several mini timesheets with indiviual employees names on. I want an employee to update there mini timesheet which will automatically update the main original timesheet. currently i have all the excel documents open and in the main timesheet i am putting in each cell = the cell from one of the mini timesheets which is taking several hours. is there a quicker way? i if this should of been posted in a differnt place could you please tell me Hi Excel can not copy stuff TO locations. She can only copy FROM places to wh...

URLs in html format
I regularly send and reply to messages from people who use HTML format. However, if I include a URL, even though it is blue and underlined, it is not live. Can you tell me what I need to do to make the URLs live in HTML message format? Many thanks, John ...

Advance Find in Activities Histories
I asked this a while back but the answer did not work, and perhaps it was that I was not clear in my description of the problem, so I will try again. I have just converted activities from 20,000 clients from Goldmine into CRM 1.2. Most of these contacts/accounts have numerous activities which we have converted to historical activities on the Activities/History Tab. When you open an Account, go to Activities, then the History tab, all the activities display. I can click on the Type title to sort them and use the page -> to move between the pages and pages of activities. In some cas...

all future times changed
I open up Outlook this morning and found all future appts recurring and one time now span two days! My jaw is bruised from hitting the table and I have no idea how to f ix this short of editing every item by hand. What could have caused this? Are you in North America? Did you run the DST patch on your O/S or from Automatic Updates? Did you remember to run the Outlook DST patch? -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "r0nbo" wrote: > I open up Outlook this morning and found all future appts recurring and one >...

Help with Formatting Spread Sheet
I have a spreadsheet that I'm currently using to enter data into each day. In this spreadsheet I want to use the gridlines. Is there a way that after I create my collums etc., that I can use the grid liines ongoing instead of cutting and pasting them from one sheet to the next? Thanks. Gridlines can be turned on and off with Tools>Options>View and either check or uncheck the box for Gridlines. However, if you are referring to borders, they are controlled in the Format facility. "SIRSTEVE" <SIRSTEVE@discussions.microsoft.com> wrote in message...

count number of entries in cell
Hi, I am trying to count the number of entries in a cell. For eg in cel A15..I have rat,cat,mat,fat....The function should return 4. Is there inbuilt function to do that or what would a macro to do that loo like. Thanks, M -- Message posted from http://www.ExcelForum.com Hi are all your entries separatet by a comma. If yes try =LEN(A15)-LEN(SUBSTITUTE(A15,",",""))+1 -- Regards Frank Kabel Frankfurt, Germany > Hi, > > I am trying to count the number of entries in a cell. For eg in cell > A15..I have rat,cat,mat,fat....The function should return 4. Is the...

How to express Between Queries in advanced find
Hi, I need to express queries such as: <entity>.<startDate> <= Today && <entity>.<endDate> >= Today Any ideas? Regards -- Dimitris ------=_NextPart_0001_087C1335 Content-Type: text/plain Content-Transfer-Encoding: 7bit <DFoukas@discussions.microsoft.com> wrote: > I need to express queries such as: > > <entity>.<startDate> <= Today && > <entity>.<endDate> >= Today Queries in Advanced Find are, by default, AND queries unless you change it to OR. So you'd just do this by putting in ...

Cell reference #6
Hey, I wanted to ask what technique excel uses for referencing? Excel refers to cells using either Column and row numbers, such as for example A1 or Cells(1, 1), or else by using range names you create. So for example you can select a particular cell (eg G15), or group of cells (say G15:K18), and name it "Sales" without the quotes. A more detailed question would get you a better answer though? -- Hth Kassie Kasselman Change xxx to hotmail "Vick" wrote: > Hey, > > I wanted to ask what technique excel uses for referencing? ...

How to put multiple data format on clipboard?
I need to put both my custom format and CF_TEXT format This is the code to copy custom format to clipboard, COleDataSource *pSource = new COleDataSource() UINT nCount = textList.GetCount(); int nSize = sizeof(TelopTextFormat) * textList.GetCount() + sizeof(long); HGLOBAL hClipMem = ::GlobalAlloc(GMEM_SHARE, nSize); LPVOID lpClipMem = reinterpret_cast<LPVOID>(::GlobalLock(hClipMem)); memcpy(lpClipMem, reinterpret_cast<LPVOID>(&nCount), sizeof(UINT)); ::GlobalUnlock(hClipMem); pSource->CacheGlobalData(CF_TELOPTEXT, hClipMem); pSource->SetClipboard(); What should I do then...

cells
what does a range of cells mean? what r the 2 different formats you can choose to print a hard copy in homework please help At least you are honest. So we can recommend the archives. And to help you should get the add in from Ron de Bruin. http://www.rondebruin.nl/ "chris" <omegafirefox@ntlworld.com> wrote in message news:0f1001c385f7$c91b9430$a001280a@phx.gbl... > what does a range of cells mean? > > what r the 2 different formats you can choose to print a > hard copy in > > homework please help Chris, A range of cells typically means one or m...

Advanced Conditional Formatting and Changing Cell Colours
I have three basic (for programmers) conditional formatting problems to solve in Excel for which I require some help. These are the last 3 conditions I need to complete for the worksheet so any help will be really appreciated. They are as follows: 1. If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell Z2 2. If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I need to change the colour of the cell containing "Z" (V2, W2 or X2). If changing the colour of only the single cell containing "Z" cannot be done, I guess changing t...

Number Formats #2
I've been working with number formats lately. I wrote in the custo format ???.??? to align a set of numbers by their decimals. Fo example, 1.25, 12.45, and 137.98 would all align by the decimal eve though they take up different amounts of text. The problem is that I want to add superscript letters after some of th numbers. So I would want an "a" to come after 1.25 in superscript. When I add the "a," the number format no longer works and the number do not align by the decimals. How can I add the letter and still make the number format ???.?? work? Thanks -- Messa...

How do I change my email server address to outlook?
I currently can not receive any incoming emails to outlook. They are going directly to the cable compnay provider. So what is the email server address I use for outlook? Check with your ISP - they normally will provide the incoming/outgoing email server addresses required for Outlook, as well as any authentication requirements. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Laura D. asked:...

ISBN #'s starting w/ "0" are deleted, how to format so "0" stays put
Hello, I need to know how to format a list of cells that contain ISBN #'s so that when an ISBN starting with a "0" the zero stays. I am working w/ Excel 97. I have tried to format the cell to "Text" but then the zeros are deleted. What I am doing is copying and pasting from another spreadsheet. So, the ISBN looks like this at first: 0-889658-27-8 Then I say "Replace "-" with nothing. So then I get: 889658278 So, what I want is: 0889658278 Got me? Any help would be greatly appreciated! Thanks! I suggest formatting the cells as text *prior* to pa...

Conditional Formatting #29
I have three columns in a spreadsheet - name, address, telephone and reason. Now in each column, I want to format the columns so that if the same, for example, address appears twice within any row in the column, the cell colour is changed to green. If it appears three time, it changes to amber and if it appears four times it changes to red. Any suggestions? One way: Assume addresses in B2:Bxxx. With B2 the active cell: CF1: Formula is =COUNTIF(B2,B:B)=4 Format1: <patterns>/<red> CF2: Formula is =COUNTIF(B2,B:B)=3 Format2: <patterns>/<yellow&...

Advanced Optimization Options
are 'Advanced Optimization' Options Inherited? like I have a app in which I have set these options and in it I am using 3rd party control of which I don't have source code nor do i know if they are using any of these options. so if I set some advanced optimization options will those also apply to that control? "Abhishek" <user@server.com> wrote in message news:i5j779$n6h$1@speranza.aioe.org... > are 'Advanced Optimization' Options Inherited? No. "Abhishek" <user@server.com> wrote > are 'Advanced O...

formatting subtotals
When using the "subtotals" function, the subtotal description gets bolded...but not the figures in the subtotal (example below). Is there anyway to change this? I want the results of the subtotal to be bolded, or otherwise highlighted. But when I collapse the spreadsheet to show only the subtotal results, and then highlight the subtotal figures and make them bold, all the detailed data in between the subtotal figures gets bold too. (these get bolded) (these do not, but I want them to be) 4140 011 2311 Total 2 4137 222 0000 Total 5 4137 223 0000 Total ...

Conditional Formatting #3
Hi all I have this conditional format in cell BB35 =IF(BB35>$BB$37,TRUE) If I insert cells between BB35 & BB37, then the formula changes to =IF(BB35>$BB$38,TRUE) Is there a way of making BB37 absolute, or is there a formula using ROW that I could use? Many thanks -- George Gee -- Select cell B35 Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =B35>INDIRECT("$B$37") Click the Format button and select your formatting Click OK, click OK George Gee wrote: > I have this conditional format in cell BB35...

Date format in Money 99
How could I change the date format to 09 may 2004 in Money 99, I tried the regional settings but I'm not sure which one to use. Thanks Try the short date settings, but I'm not sure that you can change them to get the name of the month displayed. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK specific wish. I do not respond to ...