Can i write a formula for column N in conditional formatting?
K L M N
DD 0.2 0.8
AM 0.8 1.0
TB 1.0 1.0
AM 1.0 1.0
TB 0.6 0.8
AM 0.4 0.4
AM 0.8 0.8
Any help appreciated
Supposing you want to shade a cell (says A1) with a certain background color if cell B4 meets the criteria such as has a certain value or the value is greater or less than a specific number. What would you do? This article will show you the method.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
You can do as follows to shade a cell based on another cell value in Excel.
1. Select the cell (says A1) you want to shade based on another cell value, then click Conditional Formatting > New Rule under Home tab.
2. In the New Formatting Rule dialog box, you need to:
2.1 Select Use a formula to determine which cells to format option in the Select a Rule Type box;
2.2 If you want to shade cell A1 when value of B4 is greater than 500, then enter formula =$B4>500 into the Format values where this formula is true box;
And if you want to shade cell A1 when B4 has a certain value such as “test”, the use this formula =$B4=“test”. You can change the variable in the formula based on your needs.
2.3 Click the Format button to specify a background color;
2.4 Click the OK button when it returns to the New Formatting Rule dialog box. See screenshot:
Then you can see the specified cell is shaded based on the value of another cell as below screenshot shown.