How do I change the color of a cell based on its data type (e.g. percentage or number) and range of value (e.g. 20% to 40% turn yellow, green for below 20%, and red for above 40%) in that cell? Also, how do I apply this rule to the entire sheet?
Could not use conditional formatting for this scenario (mulitiple conditions i.e. data type and multiple data ranges).
Office 2007 - Office 2010
Scenario:
The spread sheet contains same information in different ways e.g. Institution names are in row 1 and the total head conts for each institution are in row 2. then column A is for skill type. At the junction of each row and column there are number of persons in one cell and % of skilled persons out of the total head count (from row 2) in adjacent cell. And then, highlight the cells based on the criteria I mentioned above (turingin cells to green, yellow, and red based on the % range)
There is a Cell function with "format" parameter (=cell("format",A1) = "P2") that I tried to use, butm then how do I use this in AND or IF function with a check on data type and ranges of value in those cells.
In the conditional formatting tab, go to the 'New Rule' and then select 'Use a formula to determine which cell...." Enter: CELL("format", H7) = "P2" where H7 is the cell# and P2 is the decimal with two places. You can use multiple 'and' and 'or' gate operators to state the condition to be checked for formatting.
In the following condition, I am checking the range and the format (or data type) of the cell. I want to format the cells with vales between 0.505 - 1.000 and the cells that are formatted as percentages (no decimal place = P, 2 decimal places = P2, and exponential = S2). You can also store the range values in the hidden cells in the excel spreadsheet and refer (highlighted cell references) to them in the condition.
The advantage of this is that you can fill the cells with red (or any color of your choice) where for certain rows the value is between 0.5 - 1.0 (or any particular range you are interested in) and same red fill to certain rows where value range is 0 - 0.25 (or any particular range you are interested in, but different from the 1st range). I marked those rows as 'A' for ascending and 'd' for decending in the hidden cells.
So, actually, the cell is formatted conditionally based on three variables: data type of the cell to be formatted, value in the cell, and to which particular incremental order (A or D) the row belongs to.
=OR(AND(H7>=$A$2,H7<=$A$3,$A7="A",OR(CELL("format", H7) = "P2",CELL("format", H7)="P0",CELL("format", H7) = "S2")),AND(H7>=*0.505*,H7<=1,$A7="D",OR(CELL("format", H7) ="P2",CELL("format", H7) = "P0",CELL("format", H7) = "S2")))