List any two formats that can be used for conditional formatting.
Answers
When you use custom formulas in Conditional Formatting, you need to be aware of the differences between absolute and relative references. If you use CF to apply format conditions to a range of cells, any relative addresses will be translated as Excel adds the format conditions for all the cells. For example, suppose we want to apply format conditions toA1:A10 to display the cell in bold if the value inB1:B10 is greater than 10. We can use the formula =B1>10 to accomplish this. As Excel applies the Conditional Formatting to each cell in A1:A10, it will change the B1 in the formula to the proper cell value. The format condition in A7 will be =B7>10. This is generally what we would want. However, suppose we want to A1:A10 to be bold if the value in B1 was greater than 10. I.e., each cell in A1:A10 is always compared to B1. For this, we would use the formula=$B$1>10, which will not be translated as Conditional Formatting is applied to each cell inA1:A10. The format condition in A7 would remain=$B$1>10.
Array Formulas In Format Conditions
Conditional Formatting evaluates custom formulas as though they were array formula, so you may use array formulas in format conditions. You do not enter them with Ctrl+Shift+Enter in the CF dialog as you normally do in worksheet cells. Excel will always treat a custom formulas in CF as an array formula, even if it is not one.
Using Defined Names In Conditional Formatting
As noted above, custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a defined name which refers to the list in the other workbook or worksheet, and then use that name in your custom function.
For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:
=COUNTIF(MyList,A1)=0
Using Conditional Formatting To Shade Rows
You can use the Conditional Formatting tool in Excel97 and 2000 to make your worksheets look like accounting ledgers or computer "green bar" paper, with alternating bands of colors. By using Conditional Formatting rather than manually formatting the cells, the color bars will remain intact after you sort a worksheet range. Read Color Banding With Conditional Formatting for more details.
Determining Which Format Condition Is In Effect
Excel does not give you a direct way to determine whether conditional formatting is currently in effect for a cell. You must use VBA to actually test the defined conditions