Conditional Formatting – Excel 2007
Greetings. I was on hiatus from this excellent forum for some time. Got involved in too many other tasks & was unable to free myself to devote time to this forum. Now, I am seeking help on how to extend Conditional Formatting rule. The scenario is this: I have a worksheet with about 200 rows. In each row Column B thru H contain 7 numeric values. I need to flag the cell with Minimum value (change Font & color). So on Row 3, I have set up Conditional Formatting such that if the cell value is =Min($B$3:$H$3) then change Font to Bold, Italic and color Green. I have set up similarly for Row 4,5, 6. This is working fine. But I had to set up manually for each row. Is there way that I can now extend this “Conditional Formatting” to all 200 rows? I am thinking of the way that we normally use (say for extending Sum by column) by clicking on the lower right cell border and continuing to row 200. I tried – it is copying the numeric value; not copying the Formatting rule. Hope you got an idea what I am aiming for. Otherwise, I need to go Starbuck to manually type =Min($B$xx:$H$xx) for all 200 rows with a Tall order of Latte! Any help will be much appreciated.
Software/Hardware used:
Office 2007, XP, Excel
Software/Hardware used:
Office 2007, XP, Excel
ASKED:
August 15, 2010 7:19 AM
UPDATED:
October 21, 2010 9:07 AM
Answer Wiki:
1. Start Excel 2007.
2. On the Home tab, click Conditional Formatting and then click Manage Rules. The Conditional Formatting Rules Manager is shown
The Conditional Formatting Rules Manager dialog box is the one-stop place to view and manage all of the conditional format rules in a workbook.
Using this dialog, you can:
* View the rule condition, see the range it is applied to, and the formatting applied by the rule. The Show formatting rules for list at the top of the dialog box allows you to change the scope of the rules. You can view rules for the selected cells, the current worksheet, or other worksheets in the workbook.
* Add new rules with the New Rule button.
* Change the order in which rules are evaluated. This is accomplished by using the up and down arrow buttons.
* Edit existing rules with the Edit Rule button. The Edit Rule UI is the same as the New Rule UI.
* Delete rules with the Delete Rule button.
* Control whether more than one rule can evaluate to True.
When you press New Rule on the Conditional Formatting Rules Manager dialog box, or when you select More Options from the conditional formatting gallery, you see the New Formatting Rule dialog box. Now u may apply ur data.
To see all answers submitted to the Answer Wiki: View Answer History.
You could try selecting the cells you want to copy the formatting from, copy them, select the area you want to paste to, and use paste special formats.
Yes, I tried with Copy & Paste special – no luck. I am not sure, if I have described my question properly. Here is another attempt.
My Conditional Formatting rule is working fine when I develop the “rule” manually for each row. I need to get the “rule” copied for all other rows. The worksheet will have 200 to 500 rows – it is quite unwieldy to apply the “rule” manually for each row.
Like when we do the sum for one Column. After getting the 1st column sum defined properly – we just “click” on the lower bottom of the cell & then extend it to other columns – the system automatically adjusts the relative cell numbers.
I think it should be fairly easy – but I feel frustrated not being able to do it. I am attaching a copy of a Dummy spreadsheet. You may note that I have applied the rule “manually” to rows 4 thru 7. How do I extend this “rule” from Row 8 to 300?
I am sure someone has a bright idea to help me out. Many thanks in advance..
Sorry could not attach the dummy spreadsheet . How do i attach a document?
according to this site, you should be able to highlight all the cells and choose the conditional formatting button from the ribbon and then it looks pretty easy:
http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html
Have you considered removing the “$” preceding the row number?
I wanted to include “$” for absolute referencing – but still I tried by removing it – unfortunately did not work.
Thank you Frankchristopher for referring the web-site. This is a nice handy reference site for general Excel related questions. They gave example of common Conditional Formatting which (as I mentioned) is working fine. My problem is to repeat the “rule” for 300 rows. Anyway, I just laboriously completed it.
This Excel site piqued my interest in using Function. I have been using macro – but with built-in Function it makes life easier. This is truly serendipity!
Please consider my Question closed.
Note: Is it permissible to attach small spreadsheet as an example? If so, how one does? I did not see an option to attach. Just curious. Thanks.