#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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! |
#3
|
|||
|
|||
Quote:
Thanks for your assistance |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
Quote:
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. |
|
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 |
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 |