http://www.familycomputerclub.com/excel/step-by-step-excel-macros.html
http://www.ozgrid.com/Excel/
http://www.anthony-vba.kefra.http://www.ozgrid.com/Excel/
http://chandoo.org/wp/excel-vba/
Thursday, June 30, 2011
Friday, June 3, 2011
HOW TO conditionally format entire row/s based on a text value in a cell in Excel 2007
Scenario: You have a column E named "Priority". You have to highlight the rows with different colors based on whether it is "Urgent", "High" or "Normal".
1) Select the entire range of rows for which you want to apply specific background colors. If you have 1000 rows and say 10 columns of data, then select this entire range of cells.
2) In the Home tab, click on Conditional Formatting item in the Styles panel
3) Select the last option Manage Rules
4) This brings up the Conditional Formatting Rules Manager.
5) Click on the New Rule button
6) This opens up a dialog box titled New Formatting Rule. Select "Use a formula to determine which cells to format".
7) In the textbox that says Format values where this formula is true, type this formula:
=EXACT($E2,"Urgent")
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
8) Click on the Format button on the same dialog box. This opens a another dialog box Format Cells
9) Move to the last tab Fill, select a color that you want to mark as the background color and then click on OK
10) This takes us back to the original Conditional Formatting Rules Manager dialog box. When you click on the OK button, the background color is applied to 2 rows that contains the text value "Urgent".
11) To specify a background color for rows that contains the text value "High", repeat steps 3 to 9, this time specifying a different background color.
12) Repeat this procedure for other criteria (text value "Normal") and finally you have entire rows conditionally formatted.
Courtesy: http://www26.brinkster.com/mvark/codegallery/conditionally_format_entire_row.aspx
1) Select the entire range of rows for which you want to apply specific background colors. If you have 1000 rows and say 10 columns of data, then select this entire range of cells.
2) In the Home tab, click on Conditional Formatting item in the Styles panel
3) Select the last option Manage Rules
4) This brings up the Conditional Formatting Rules Manager.
5) Click on the New Rule button
6) This opens up a dialog box titled New Formatting Rule. Select "Use a formula to determine which cells to format".
7) In the textbox that says Format values where this formula is true, type this formula:
=EXACT($E2,"Urgent")
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
8) Click on the Format button on the same dialog box. This opens a another dialog box Format Cells
9) Move to the last tab Fill, select a color that you want to mark as the background color and then click on OK
10) This takes us back to the original Conditional Formatting Rules Manager dialog box. When you click on the OK button, the background color is applied to 2 rows that contains the text value "Urgent".
11) To specify a background color for rows that contains the text value "High", repeat steps 3 to 9, this time specifying a different background color.
12) Repeat this procedure for other criteria (text value "Normal") and finally you have entire rows conditionally formatted.
Courtesy: http://www26.brinkster.com/mvark/codegallery/conditionally_format_entire_row.aspx
Prevent duplicate entries in a column
- Select the range of cells in which you want to prevent duplicates (for example, B2:B1048576).
- From the Data menu select Validation.
- Click on the Settings tab.
- From the Allow dropdown box select Custom.
- In the formula box type =COUNTIF($B$2:$B$1048576,B2)=1 (see explanation below)
- Click on the Error Alert tab.
- In the Title box type "Duplicate Entry!"
- In the Error Message box type "You cannot enter a value already in the list."
Explanation:
This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($B$2:$B$1048576,B2)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert.
Subscribe to:
Posts (Atom)