copy conditional formats #2

I've spent the past 2 days researching this issue, but have come up dry.
I'm hoping you can help me.

I have a range of cell, 2 abreast in this instance, and I have placed
conditional formatting on them.

1) if B2 = x than range (B2:D2) is green
2) if B2 = y than range (B2:D2) is yellow
3) if B2 = z than range (B2:D2) is red

...

1) if B6 = x than range (G6:I6) is green
2) if B6 = y than range (G6:I6) is yellow
3) if B6 = z than range (G6:I6) is red


This works great.

My quandary is "copying" this conditional formatting to other cells.

( Column B->D Rows 2->26 ) and ( G->I Rows 18->40 ) need the same
formatting, but each row is based on the value of it first column of
the set (B & G) of that row.

I've used PAINTER. I've used PASTE->Special.

All these options copy the formatting alright, but it does not change
the referential cell. It all points back to B2.

I have a pretty large sheet I would like to format this way, and it
would take hours to hand re-format each range.

Not to mention when I add more to this set, and I will.

Do you have any tricks you can share.

Thanks again for your help.

Walter



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
11/2/2003 12:28:42 AM
excel 39879 articles. 2 followers. Follow

1 Replies
475 Views

Similar Articles

[PageSpeed] 45

You could do it in two steps:

Select B2:D26
(keep B2 the activecell)

Write your conditional formatting formula using B2, but use $b2 as the
reference.  Each of the other rows will get the same formula (offset to match
its row), but the $B will mean that it's always looking at the value in column B
of that row.

Then do the similar thing for G18:I40 (Use $g18 as the reference.)



excelWalter wrote:
> 
> I've spent the past 2 days researching this issue, but have come up dry.
> I'm hoping you can help me.
> 
> I have a range of cell, 2 abreast in this instance, and I have placed
> conditional formatting on them.
> 
> 1) if B2 = x than range (B2:D2) is green
> 2) if B2 = y than range (B2:D2) is yellow
> 3) if B2 = z than range (B2:D2) is red
> 
> ..
> 
> 1) if B6 = x than range (G6:I6) is green
> 2) if B6 = y than range (G6:I6) is yellow
> 3) if B6 = z than range (G6:I6) is red
> 
> This works great.
> 
> My quandary is "copying" this conditional formatting to other cells.
> 
> ( Column B->D Rows 2->26 ) and ( G->I Rows 18->40 ) need the same
> formatting, but each row is based on the value of it first column of
> the set (B & G) of that row.
> 
> I've used PAINTER. I've used PASTE->Special.
> 
> All these options copy the formatting alright, but it does not change
> the referential cell. It all points back to B2.
> 
> I have a pretty large sheet I would like to format this way, and it
> would take hours to hand re-format each range.
> 
> Not to mention when I add more to this set, and I will.
> 
> Do you have any tricks you can share.
> 
> Thanks again for your help.
> 
> Walter
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/2/2003 12:47:01 AM
Reply:

Similar Artilces:

Excel Regional Date Format Options
A client of ours in NZ is complaining that date format options for English (New Zealand) have changed from older versions of excel (they are using 2003) Some of their spreadsheets have dates formatted as dd-mmm-yy, mmm-yy and dddd,dd,mmm but these options do not exist anymore. Is there anyway to add options to this list without using the custom format option? Thanks, Jesse I just compared the Excel 97 and Excel 2003 built-in date formats and they are mostly unchanged. 2003 has a few more but I don't think there were any subtractions. The formats dd-mmm-yy and mmm-yy are righ...

Copying and Pasting Between Published Documents
Hi I have created a 132 Document in Publisher 2007 I needed to make a highly customised and intelligent Index so I created a second document and typed the index in that, having both documents open on extended Windows Pages I thought it would be easy to move the index from the second document into the first document containing the text It does not appear possbible to copy and paste table pages between the two publisher documents Is there any way I can accomplish this task without retyping the Index in the first document Roger Stenson Roger, 1. Create on your 132 page document a bla...

Why does copy and paste between eMails not work?
Copy no longer copies to the clipboard. Is there a setting I have deleted somewhere? Using Windows 7 and Office 2007 proffessional. Does it work if you restart outlook? Do you have any addins installed that could be erasing the clipboard? I've seen this happen with addins and contacts, but not email. to test, open the new message form before copying so you just need to copy and switch to the other message. (http://www.slipstick.com/Contacts/clipboard.htm) -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

How Do I Copy My Outlook Files To My New Computer?
I exported my Outlook files from my old computer and burned them to a CD. During the export process, it asked me three times for a password. When I tried to import these files to my new computer I got "File access is denied. You do not have the permission required to access the file." Can anyone help? Apart from the fact that Import/Export options often lead to corruption - you should use a simple pst copy - The pst needs to be on the hd and the read only flag off, before In OL attempting to open it. NB not import <aprice6@hotmail.com> wrote in message news:1145746658.84...

Excel sheet truncated after copying to powerpoint slide
Hi, We are using Office 2000 with sp3. When we copy excel sheet to power point slide, some of the rows and colums are truncated. Also the font size is changed. We can see only some part of the sheet. Let me know if anybody faced this problem.. Thanks in advance Shekar. Debra Dalgleish posted this link that you may want to review: http://www.rdpslides.com/pptfaq/FAQ00068.htm Microsoft wrote: > > Hi, > > We are using Office 2000 with sp3. When we copy excel sheet to power point > slide, some of the rows and colums are truncated. Also the font size is > changed. We can s...

Microsoft Money Deluxe 2004 #2
How do I install on a drive other than C? I am running Windows XP Home Edition. In microsoft.public.money, Don wrote: >How do I install on a drive other than C? I am running Windows XP Home >Edition. Early in the install process, Money has a place to click to change where the installation is to take place. If you install in a drive other than the system drive, there could be an issue. I was thinking there might be, but I don't find a reference. http://support.microsoft.com/kb/79917/en-us implies that should work. There is no problem. I have always installed Money (from M...

Outlook startup #2
All of a sudden all our outlook client (Outlook2002/Exchange 2000) are getting the following message: "Your microsoft exchange server is unavailable" with the retry and cancel options. We hit the retry option it connects to the exchange. Any ideas???? Sounds like name resolution problems. Can you ping the Exchange server, by name, from the affected workstations? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup....

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

NO #2
Seriously Microsoft, what the fuck? I'm not saying this as some sort of mac fan or money zealot or whatever. Hell, I even worked for you once... but.. YOU ARE DEAD TO ME! Fine, stop selling Money, I don't care. BUT LEAVE YOUR GOD DAMNED SERVERS UP SO WE CAN GET ONLINE UPDATES INDEFINITELY. How much could that cost you a month? A couple thou? You throw more than that away every second repairing peoples broken Xbox 360s. This is a new freakin low. Seriously. ...

Moving Email Domain off of Exchange #2
Good afternoon, I am in the process of moving one of our clients off of our Exchange server and on to a third party email host. I have removed there domain from the default recepient policy, and also disabled and unattached or deleted their mailboxes. I have repointed there MX record to point to the new host etc. The problem is when I try to email them from our network my exchange server still thinks it is responsible for handling the mail for the domain and does not try to send it out of the domain. I get a 5.1.1 e-mail account does not exist in this organization. How do I let Exchange ...

Combine 2 rows if name is same in Column B & C on both
Combine 2 rows if name is same in Column B & C on both 2 spreadsheets - Sheet 1 is bigger with extra names in column B & C Lastname Firstname Both - Column B & C Lastname Firstname - both sheets Sheet 1 has data in Col. D & E Sheet 2 has data in Col. F & G Sheet 1 has extra names not in Sheet 2 If Sheet 1 B&C = Sheet 2 B&C , then add F&G columns from sheet 2 , behind D& E columns on sheet 1 , for the match of names in Column B & C Thanks kerns.walter@epa.gov On Nov 13, 12:50 pm, wk <kerns.wal...@epa.gov> wrote: > Combine 2 rows if name...

SDK for Microsoft POS 2.0
Does anyone know of an SDK for MS POS 2.0? I have noticed several companies making add-ons for modules for MS POS, are they standalone products that integrate with the POS database or are they actually integrated into the MS POS itself? Thanks for your comments. On Nov 22, 4:46=A0am, NetKnight <NetKni...@discussions.microsoft.com> wrote: > Does anyone know of an SDK for MS POS 2.0? > Thanks for your comments. they are standalone products integrated with the database, and also add-on integrated with SO POS/SO Manager via COM antonio ...

View both incoming/outgoing messages by Thread or Conversation ? #2
Is it possible to view messages in outlook 2003 by Thread or Conversation, both incoming and outgoing, in a single folder, or Inbox, just like Outlook Express News Reader. For example: + Sent Message + Received Reply +Sent Message or Replied + Received Reply I tried using Arrange by Conversation Option in outlook 2003 Inbox Folder, but its just showing Inbox Messages, not Sent Items Messages. Best Regards, Luqman ...

Compile Error in hidden module: Autoexec #2
I need help when ever I opepen or close any office product ( excel , word etc...) I get the message shown above.... How do I fix it please help ag Take a look at this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 HTH Anders Silven "Ash" <ashrafg@sympatico.ca> skrev i meddelandet news:1574f01c446a0$4b5db2c0$a501280a@phx.gbl... > I need help when ever I opepen or close any office > product ( excel , word etc...) I get the message shown > above.... How do I fix it please help > > ag ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

Publisher 2003 files try to open in Publisher 97 #2
Back again, but with a different Pub 2003/Pub97 issue Have Office 2003 with Publisher 2003 and Publisher 97 both on my XP Pro system Recently I find that files created in Pub 2003 are being saved as files with the Pub 97 Logo, and when you try to open them from the file listing, they open Pub 97, which correctly posts a box telling me that it cant open the files. If I use Publisher 2003 to open the same files from within the application the work fine. When I select a 2003 Pub file which contains the Pub 97 icon, the file list does not contain Publisher 2003, but when I browse and locat...

Won't Remember Password #2
My email account calls for the password everytime I use it. Even when I check the box to remember my password, it won't retain it. I run XP. Anyone else having problems with this? If you are still having problems after applying both the SP 1 & 2 = patches, then see if this article addresses your problemL OL2002: The Save Password Setting Is Not Saved When You Connect to a = POP3 Server http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;290684 --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups...

uploading 2 or more mailboxes on 1 user account
I was able to reconnect 1 mailbox to 1 user account, but what i' looking is the way wherein i can reconnect 2 or more mailboxes into user account because instead of giving a 1:1 user account to eac person we will be replacing it and giving them an account per area Hence they already have their own personal account we will b deleting it; How can i put all their messages from their persona account into the single account that we wil be giving to them Hi, More than one mailbox per account is not possible in Exchange 200x. You can do that with Exchange 5.x Regards, -- Menko den Ouden ...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

VFW, webcam, image format
Hello, I'm trying to grab pictures from a webcam with Video For Windows (VFW). The source code I use is the following: HWND _capwnd = capCreateCaptureWindow("Capture",WS_POPUP,0,0,1,1,0,0); capDriverConnect(_capwnd,0); capGrabFrame(_capwnd); capEditCopy(_capwnd); OpenClipboard(NULL); HBITMAP hBmp = (HBITMAP)GetClipboardData(CF_BITMAP); CloseClipboard(); BITMAP bmp; GetObject(hBmp, sizeof(BITMAP), &bmp); int size = bmp.bmWidth*bmp.bmHeight*bmp.bmWidthBytes; unsigned char* data = new unsigned char[size]; GetBitmapBits(hBmp,size, data); ...

Don't know where else to put q's. 2 questions. 11g Dongle and wire
What is and Acer WLAN 11g USB Dongle? It is some program that is on my computer and pops-up at start-up. I'v double clicked it, and nothing happens. I went on Acers site, and I can find nothing. Even some freeware mentions it, but they only talked about the freeware, not this WLAN 11g itself. And how can I tell if I have wireless or not. This seems like a nobrainer, you have it or not. But some documents on my computer mention wireless internet surfing in programs that are there. I use a high speed connection through an external modem that connects to my comuter and the w...

Database problem #2
Hi guys, I wrote an application that reads about 50.000 records from many files and wrote these in a db. This application is really slow. Can the cause of this slowness be the reading from the files? If yes, can I resolve with 2 thread where one will makes a cache of records reads from the files and the other writes the data in the db? Tnx It's more likely to be the writing in the db that's slow, assuming that you're not processing the files as you open them. One thing that might help in this case is to close any open indexes before adding the new records, and re-create the in...

michael lalonde #2
michael lalonde mike lalonde sudbury wanderer tax solutions 3665 Arista Way http://www.freewebs.com/wandererartificialstupidity/ http://www.geocities.com/wandererartificialstupidity http://wandererartificialstupidity.tripod.com http://members.aol.com/lalonm/ http://www.wanderers.freewebweb.com/ Wanderer Tax Solutions 702 Barrydowne Road - Unit 9 Sudbury, ON P3A 3T5 705 562 2399 Barrydowne Road Sudbury, Ontario. Second Location. 1630 Agincourt Avenue Sudbury, ON. wanderer rampar wdr c64 commodore 64. jason john lalonde mike lalonde sudbury temagami lalonde jason tesker angela legrow darryl...