Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-21-2009, 01:08 AM
Niko Niko is offline
Novice
Cell formatting
 
Join Date: Apr 2009
Posts: 3
Niko is on a distinguished road
Default Cell formatting

Hey guys I wonder if someone would be able to help me.



I am currently trying to create a table in Excel. I have managed to create the table but now I have hit an impasse. I am trying to set it up, so that whenever I enter ONLY the letters of "Y" or "N", the table boxes are highlighted green for a Y, and red for an N.

I thought I had managed to do it, but then when I was typing things into the other boxes, if any word had an N or a Y in it, these boxes would be altered to the appropriate color too and my table turned into reds and greens all over the shop

Can anyone offer any assistance with this? I hope i've explained it properly. Oh, and I am using Excel 2007 too


Thanks guys.
Reply With Quote
  #2  
Old 04-21-2009, 01:46 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Cell formatting Cell formatting Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Thumbs up

If you want the conditional formatting to work with Y/N then you need to use:

Home (tab) > Conditional Formatting > Manage rules > New Rule > Format only cells that contain > [Cell Value - equal to - Y] > Format [choose whatever you want]
Follow the steps from 'New Rule' again to do the other Conditional Format.

I think what's happened is that you used the 'Text that contains ...' option - meaning that ANY word with Y/N in will activate the rule! Ooops!
Reply With Quote
  #3  
Old 04-21-2009, 02:03 AM
Niko Niko is offline
Novice
Cell formatting
 
Join Date: Apr 2009
Posts: 3
Niko is on a distinguished road
Default

Quote:
Originally Posted by Bird_FAT View Post
If you want the conditional formatting to work with Y/N then you need to use:

Home (tab) > Conditional Formatting > Manage rules > New Rule > Format only cells that contain > [Cell Value - equal to - Y] > Format [choose whatever you want]
Follow the steps from 'New Rule' again to do the other Conditional Format.

I think what's happened is that you used the 'Text that contains ...' option - meaning that ANY word with Y/N in will activate the rule! Ooops!
Yes I do remember using "text that contains"....i will give your suggestion a go and let you know if it works.

Thanks for your assistance
Reply With Quote
  #4  
Old 04-21-2009, 02:54 AM
Niko Niko is offline
Novice
Cell formatting
 
Join Date: Apr 2009
Posts: 3
Niko is on a distinguished road
Default

Actually, while we're on the topic, I wonder if I may post a question

If you have specific rules created, as in my case, any cell that contains "N" make it red, any that contain "Y" make it green.

Now, if you are constanty adding in Y's and N's to the sheet, and then copying the tables each subsequent week and only changing the dates....does it also copy the rules for the new table??

The reason I'm asking is because we have such a sheet in place. We copy the data and change the Y's & N's appropriately for each day of the week, and only the dates change, nothing else.

This spreadsheet has recently become dog slow, doesn't function properly and crashes out whenever you try to copy and paste a new table. Because you showed me how to create the rules for the cell formatting, I went in and had a look.....and there must be over 1000 rules in there, add to the fact there are 7 other worksheets within the spreadsheet that use the same rule, so they all have there own 700 or 1000 number of rules.

This must be why the sheet has become unworkable, slows and crashes out my machine. Can I delete all the rules and just add in two new rules for Y and N appropriatly? Or will it just start creating copies of these rules each week we copy the table??

Sorry for the long winded post, but this will help in not needing to create a new sheet if its the excessive number of rules that are causing this problem.

Cheers
Reply With Quote
  #5  
Old 04-22-2009, 07:11 PM
praveen_p praveen_p is offline
Novice
 
Join Date: Apr 2009
Posts: 3
praveen_p is on a distinguished road
Default You can use this format

Yes this format works check it once.
Home (tab) > Conditional Formatting > Manage rules > New Rule > Format only cells that contain > [Cell Value - equal to - Y] > Format [choose whatever you want]
Follow the steps from 'New Rule' again to do the other Conditional Format.
Reply With Quote
  #6  
Old 04-23-2009, 12:05 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Cell formatting Cell formatting Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

Quote:
Originally Posted by Niko View Post
Now, if you are constanty adding in Y's and N's to the sheet, and then copying the tables each subsequent week and only changing the dates....does it also copy the rules for the new table??
It should indeed do!

As to all these hundreds of rules - if you are only using the rules you have just made, then delete the rest! I would suggest that you save a copy of the original document, then in the copy you can delete ALL the rules and add back in your new ones - does it effect the working of the workbook? if NO - go for it, if YES, start again and delete a handful at a time - that way you can work out needed vs unneeded.
Have extraneous rules floating around in your workbook can clog it up as it is trying all the rules, therefore wasting time.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto-populate an MS Word table cell with text from a diff cell? dreamrthts Word Tables 0 03-20-2009 01:49 PM
Cell formatting Autosizing picture to fit a cell Lor66 Excel 1 12-05-2008 07:33 PM
Drop down box in a cell Grapejuice Excel 0 10-22-2007 07:40 PM
highlight cell after checkbox flatk Word 0 01-25-2007 12:32 PM
click a cell to go to a worksheet victor Excel 0 10-27-2006 02:57 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft