Conditional Formatting – Excel 2007

2540 pts.
Tags:
Conditional formatting
Excel 2007
EXTEND mode
Microsoft Excel formatting
Microsoft Windows XP
Office 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
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Chippy088
    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.
    4,625 pointsBadges:
    report
  • SbElectric
    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?
    2,540 pointsBadges:
    report
  • Frankchristopher
    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
    30 pointsBadges:
    report
  • BCP
    Have you considered removing the "$" preceding the row number?
    65 pointsBadges:
    report
  • SbElectric
    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.
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: