conditional formatting in Excel 2007 - help, please!

I have a large spreadsheet, with the active data area being cells a4:x8659.
In the A column, I have a formula that evaluates to 1 or 0, depending on 
whether the room number in column E has changed from the previous row.

What I *want* to have happen is this:
1) If the value of the cell in the A column for a particular row is 1, set 
the color of the cells in that particular row to color #1 (in this case, a 
nice pastel yellow); 
2) If the value of the cell in the A column for a particular row is 0, set 
the color of the cells in that particular row to white. 

Have tried to follow the instructions in the Help section, but am not 
getting the results I want - yet. 

The range of cells to be checked and colored *is* large. Is this a factor in 
getting the CF to work?

If I've understood the Help File correctly, this conditional formatting can 
be over-ridden by separately and manually-applied cell formatting. Am I 
correct on this?

Any help will be appreciated - greatly!

Barbara
-- 
***************************
Bookaholics of the World, Unite!
You have nothing to lose but your ignorance!
***************************
0
barbara (41)
7/30/2008 7:30:05 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
493 Views

Similar Articles

[PageSpeed] 54

You should be able to do the following

=INDIRECT("A"&ROW())=1

=INDIRECT("A"&ROW())=0

Enter those formulas into conditional formatting and apply the formatting 
you want to each. (Yellow and white is what you want). 

This formatting will need to be applied to all cells in your worksheet you 
want colored.

"Barbara" wrote:

> I have a large spreadsheet, with the active data area being cells a4:x8659.
> In the A column, I have a formula that evaluates to 1 or 0, depending on 
> whether the room number in column E has changed from the previous row.
> 
> What I *want* to have happen is this:
> 1) If the value of the cell in the A column for a particular row is 1, set 
> the color of the cells in that particular row to color #1 (in this case, a 
> nice pastel yellow); 
> 2) If the value of the cell in the A column for a particular row is 0, set 
> the color of the cells in that particular row to white. 
> 
> Have tried to follow the instructions in the Help section, but am not 
> getting the results I want - yet. 
> 
> The range of cells to be checked and colored *is* large. Is this a factor in 
> getting the CF to work?
> 
> If I've understood the Help File correctly, this conditional formatting can 
> be over-ridden by separately and manually-applied cell formatting. Am I 
> correct on this?
> 
> Any help will be appreciated - greatly!
> 
> Barbara
> -- 
> ***************************
> Bookaholics of the World, Unite!
> You have nothing to lose but your ignorance!
> ***************************
0
JBoyer (1)
7/30/2008 8:16:33 PM
>2) If the value of the cell in the A column for a particular row is 0,
>set the color of the cells in that particular row to white.

If you're using the default fill color which is "no fill color" then the 
cells already have a white fill color.

Select the range A4: X8659
Goto Home tab>Styles>Conditional Formatting>Manage Rules
Click New Rule
Select Use a formula to determine.....
Enter this formula in the little box:: =$A1=1
Click the format button
Select the desired style(s)
OK out


-- 
Biff
Microsoft Excel MVP


"Barbara" <Barbara@discussions.microsoft.com> wrote in message 
news:C5C999C4-9BC8-4B63-9C0E-B16AA98D2D49@microsoft.com...
>I have a large spreadsheet, with the active data area being cells a4:x8659.
> In the A column, I have a formula that evaluates to 1 or 0, depending on
> whether the room number in column E has changed from the previous row.
>
> What I *want* to have happen is this:
> 1) If the value of the cell in the A column for a particular row is 1, set
> the color of the cells in that particular row to color #1 (in this case, a
> nice pastel yellow);
> 2) If the value of the cell in the A column for a particular row is 0, set
> the color of the cells in that particular row to white.
>
> Have tried to follow the instructions in the Help section, but am not
> getting the results I want - yet.
>
> The range of cells to be checked and colored *is* large. Is this a factor 
> in
> getting the CF to work?
>
> If I've understood the Help File correctly, this conditional formatting 
> can
> be over-ridden by separately and manually-applied cell formatting. Am I
> correct on this?
>
> Any help will be appreciated - greatly!
>
> Barbara
> -- 
> ***************************
> Bookaholics of the World, Unite!
> You have nothing to lose but your ignorance!
> *************************** 


0
biffinpitt (3172)
7/30/2008 9:56:48 PM
Ooops, typo!

> Select the range A4: X8659
> Enter this formula in the little box:: =$A1=1

The correct formula should be: =$A4=1

-- 
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:exHns8o8IHA.5404@TK2MSFTNGP04.phx.gbl...
> >2) If the value of the cell in the A column for a particular row is 0,
>>set the color of the cells in that particular row to white.
>
> If you're using the default fill color which is "no fill color" then the 
> cells already have a white fill color.
>
> Select the range A4: X8659
> Goto Home tab>Styles>Conditional Formatting>Manage Rules
> Click New Rule
> Select Use a formula to determine.....
> Enter this formula in the little box:: =$A1=1
> Click the format button
> Select the desired style(s)
> OK out
>
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Barbara" <Barbara@discussions.microsoft.com> wrote in message 
> news:C5C999C4-9BC8-4B63-9C0E-B16AA98D2D49@microsoft.com...
>>I have a large spreadsheet, with the active data area being cells 
>>a4:x8659.
>> In the A column, I have a formula that evaluates to 1 or 0, depending on
>> whether the room number in column E has changed from the previous row.
>>
>> What I *want* to have happen is this:
>> 1) If the value of the cell in the A column for a particular row is 1, 
>> set
>> the color of the cells in that particular row to color #1 (in this case, 
>> a
>> nice pastel yellow);
>> 2) If the value of the cell in the A column for a particular row is 0, 
>> set
>> the color of the cells in that particular row to white.
>>
>> Have tried to follow the instructions in the Help section, but am not
>> getting the results I want - yet.
>>
>> The range of cells to be checked and colored *is* large. Is this a factor 
>> in
>> getting the CF to work?
>>
>> If I've understood the Help File correctly, this conditional formatting 
>> can
>> be over-ridden by separately and manually-applied cell formatting. Am I
>> correct on this?
>>
>> Any help will be appreciated - greatly!
>>
>> Barbara
>> -- 
>> ***************************
>> Bookaholics of the World, Unite!
>> You have nothing to lose but your ignorance!
>> ***************************
>
> 


0
biffinpitt (3172)
7/30/2008 10:07:08 PM
Reply:

Similar Artilces:

Emailing in excel 2003 02-26-10
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...

outlook 2007 runs slow
Hi Outlook 2007 runs slow. Is there a way to speed it up. Thanks Do you have the outlook performance update installed? Are you using a Dell laptop? Did you upgrade from an older version and are still using that profile? http://www.slipstick.com/outlook/ol2007/slow.htm - install the update listed at the end - also make sure all OS and Office updates available through Microsoft update are installed. If it's still slow, try the various solutions that apply to your situation. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Ta...

Formula help... #3
In column A there are cells with team names in them like the following: NYY NYY NYY @OAK @OAK @OAK BOS BOS BOS @TEX @TEX @TEX I would like column B to list all the team names without the @ symbol and column H to list all the teams with the @ symbol. For the example I would like column B to have NYY NYY NYY BOS BOS BOS =COUNTIF(A:A,"@*") and =COUNTIF(A:A,"<>@*") -- HTH RP (remove nothere from the email address if mailing direct) "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:135965C4-7523-47AB-88DE-D0B12B652CD5@m...

Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...

VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...

outlook 2000 help file
Seems after I did added SP3 from MS upgrade site my help from outlook has quit working. message is msohelp caused error in itss.dll. help works in ALL other programs including outlook address book. I'm running windows 98 SE, Office 2000 SB and IE 6. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.542 / Virus Database: 336 - Release Date: 11/18/03 ...

EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...

Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...

Copying conditional formating
Question: I want every cell in row 2 to have red background if its value is different than the corresponding cell in row 1. I can do this to cell A2 if in the conditional formatting field I say =A2<>A1 but when I copy the formatting (using the brush), the condition continues to be A2<>A1 and not B2<>B1 for cell B and so forth. How do I copy the conditional formatting adjusting the references like the copy cells command do? Thanks. 1) Select all relevant cells in row 2. 2) Choose "Conditional Formating" > by Formula > type: =A2<>A1 > dec...

Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...

Exchange 2007 Evaluation version #2
Can I install it on 64 bit platform and run it as production? On Fri, 23 Feb 2007 09:55:13 -0800, RG <RG@discussions.microsoft.com> wrote: >Can I install it on 64 bit platform and run it as production? There isn't an evaluation version as such. You can install Exchange 2007 without a key for 120 days. ...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

Excel to check for regular expression?
Hi, How do you use Regular expression in excel? For eg: Check a column of data to see whether are all of them valid email addresses? Please advise THanks By valid, do you mean that the email is in proper syntax or that it is an actual email address. Text formulas can be used for proper syntax. Perhaps a Visual Basic proceedure. I doubt you can check that the email address actually exists, as many companies and ISPs will simply disregard and drop mail to invalid email addresses to eliminate spam "user" wrote: > Hi, > > How do you use Regular expression ...

Please Help
I keep getting this Problem Event Name: BlueScreen OS Version: 6.1.7600.2.0.0.256.48 Locale ID: 1033 Additional information about the problem: BCCode: 1a BCP1: 00005003 BCP2: 8A000000 BCP3: 00005BFD BCP4: B67AF009 OS Version: 6_1_7600 Service Pack: 0_0 Product: 256_1 Files that help describe the problem: C:\Windows\Minidump\122609-26379-01.dmp C:\Users\Johnbebe\AppData\Local\Temp\WER-38891-0.sysdata.xml Read our privacy statement online: http://go.microsoft.com/fwlink/?link...8&clcid=0x0409 If the online privacy statement is not available, please read our p...

Does anyone know where I can download EXCEL.MSI?
I am missing my Excel 2002 CD, and can't install Office Update Service Pack 3 without this file. ...

Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...

connect a form to excel database
I have a database in excel which lists our stock and prices etc. I would like to be able to enter a stock number on an invoice that will retrieve the details and price from my excel database. Can this be done? Excel has built in option--try data|form. If that doesn't quite work, but it's close, John Walkenbach has an enhanced dataform at: http://j-walk.com/ss/dataform/index.htm And if you want to design your own, Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html Kathy wrote: > > I have a database in excel which lists our stock and p...

Multiple conditional formating for one cell
I have a workbook with several sheets. Sheet1 has several hundred rows of text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2 I have I have a cell that allows my users to free hand type in a keyword(s) to search for in Sheet1. This cell is also has a reference name of "Special_Char". Now comes the question..... I like to for the user to type the keywork into "Special_Char" and any or all the rows in Sheet1 that contain the keyword as part of its text to turn yellow. Is there a way to do this? Please keep in mind that the keywork i...

Phone # Format
Can somebody help me with applying a format to the phone field. the default format seems to be ######### and I am looking to use (###) ###-#### Thanks in advance Dennis, I think it isn't possible at the moment. You could use the sdk and try to use postcallouts which call a webservice which puts the data in a special format and sends it back. Steffen "Dennis" <dennis_ess@hotmail.com> schrieb im Newsbeitrag news:uFrmFMGNEHA.3400@TK2MSFTNGP09.phx.gbl... > Can somebody help me with applying a format to the phone field. the default > format seems to be ######### and...

In Excel program shows a broken line and don't know how to get ri.
I am trying to put in entries in Excel program and when I go to Print Preview, it only shows part of the entries I put in. When I go to the last entry that it shows in Print Preview, there is a broken line between it and the next entry. I don't know what I did. I could use some help You probably have multiple areas selected for your print area. you can check this by <view><page break preview> One possibility to correct this would be <file><print area><clear print area> or specifically select the area you want printed and <file ><Print area>...

Excel Cells Contents to HTML Pages
Hi, I have an excel sheet which has two columns. Column 1: PageName Column 2: HTMLCode PageName has information like: OurServices.htm which is the name of th HTML page. HTMLCode clumn has actual HTML Code like: <html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta name="GENERATOR" content="Microsoft FrontPage 5.0"> <meta name="ProgId" content="FrontPage.Editor.Document"> <meta http-equiv="Content-Type" content="text/html charset=windows-1252"> ...

Office 2007 Compatibility Pack 12-10-09
My new Windows 7 laptop came with a trial version of Office 2007 (Student), which I uninstalled prior to first use. However, I have the PowerPoint Viewer installed, which I use. Question: There's a huge 173 MB Office Compatibility Pack in the Control Panel, Programs and Features, which can be uninstalled. Can I uninstall this without any effect on the PowerPoint Viewer or Microsoft Works (or Windows Live Mail) programs? Thanks. EW Hi, Lone Star, The compatibility pack is actually quite useful if you are running a previous version of Office. Are you only using W...