What I would like is: if ANY cell has a number greater than 1 - then the next 3 cells TO THE RIGHT trun red

Is there a way of doing this, without me having to type the formula in to every single cell?

Please help

  • 1
    To phrase this another way, if the cell three columns to the left of this one contains a value > 1, then this cell should be red. I don't have an answer, but expressing it this way makes it easier to comprehend in my mind at least... fake edit: just realised this would have to be expressed as 'if any of the three cells to the left of this cell are > 1 then this cell should be red. I think.
    – toryan
    Oct 23, 2013 at 20:32

1 Answer 1


What @toryan said is correct. I would start by creating a conditional formatting rule that checks the three cells to the left of the active cell for a value greater than 1.

  1. select a cell, for example G1. Go to conditional formatting >> new rule.
  2. Choose "use a formula to determine which cells to format."
  3. For the formula, use =(D1>1) + (E1>1) + (F1>1). This is equivalent to asking "is one of the three cells to the left greater than 1".

Then you can use format painter to quickly copy this rule to all of the other cells in the sheet. Select the cell in which you've placed the formula. Click the format painter on the home tab, and then select all of the cells in which you want the formula pasted.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .