I have two columns. One column has the tier as Prime,essential,maintenance and Consumption.
Other column has values in either % or as NA.
I want to format all cells which are having tier as Prime and essential and value is >=90 as green else Red.
Similarly if tier is maintenance or Consumption and value is >= 96 then green else Red .
How do i do it. Please help
Select the range that you will work with;
Click the Home > Conditional Formatting > New Rule….
In the Format Only Cells with section, specify the conditions that you need.
Click the Format button.
In the Format Cells dialog box, set the background color under Fill tab.
More details here
For your conditional formatting just select a column go to:
conditional formatting>highlight cells rules>less than at the value you put 90 and at highlight you choose red
then again:
conditional formatting>highlight cells rules>greater than and at the value you put 89 and at color green
UPDATE:
For more conditions go to new rule and choose use a formula. The formula will look like this for the green color: =AND(A1="Prime",B1>90) then you do the same steps for the other conditions and colors.
As I understand it, all cells will be one or other colour, which means only one colour need be set by Conditional Formatting. (The other can be applied with 'standard' fill, leaving the CF rule to override it where applicable).
It might be easier to fill 'standard' with green and use CF for red but the formula rule for green (ie formatting the whole range red with standard fill) might be:
=OR(AND(OR($A1="Prime",$A1="essential"),$B1>=0.9,NOT(ISTEXT($B1))),AND(OR($A1="maintenance",$A1="Consumption"),$B1>=0.96,NOT(ISTEXT($B1))))
This assumes the tiers are in ColumnA and the percentages are in ColumnB (where the value 1 represents 100%, even if formatted to appear as 100%). The Applies to range should be =$A:$B or a number of rows in those columns, to suit. The Applies to range may be set by selecting ColumnsA:B before entering the CF formula rule.