-1

How to format a range base on previous column value. Ex: A2=33% (is Best Before Date); B2 is 33%, C2 is 31% (to be format in red due to less than A2=33%)

1
  • so you want both B2 and C2 to be formatted red? – Forward Ed Sep 14 '20 at 17:33
0

Select the range that you want the conditional formatting applied to and ensure the top left most cell is the active cell of the selection. It will appear to have a white/bright background compared to the rest of the selection.

enter image description here

With the range still selected, go to the ribbon, home tab, Styles sections and left click on Conditional formatting. From the menu that appears, select new rule:

enter image description here

From the window that appears, select the entry at the bottom of the list "Use a formula to determine which cells to format".

enter image description here

In the formula box below, enter the following formula:

=$A1>B2

enter image description here

Note the $ before the A. This will keep the A from changing to B and C and so on in the background as the conditional formatting rule is applied. B2 will update across the selected range and corresponds to the active cell picked when selecting the conditional formatting rage at the start.

Click the format button in the lower right side, and pick all the formatting you want applied when the result of the formula you entered is TRUE.

enter image description here

In this example I am colouring the font RED

enter image description here

And the end result is as follows:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.