I'm trying to get a cell C3 to highlight using conditional formatting, but based on a value in 4 other cells.

If 4 continuously adjacent cells (e.g. AS3, AT3, AU3, AV3) contain the X value only then do I want the cell C3 to highlight, so NOT if they are not joined together.

(It's a worksheet to highlight absences, 4 times in a row).

How would I go about this?

  • I just answered the question for 4 adjacent cells, but then read it again and think I may have misunderstood. I read it as you were looking to check those 4 cells given. OR is this a whole row of cells involved where you are searching for 4 consecutive x vales within that row? Can you please clarify. – CharlieRB Oct 25 '13 at 11:59

Let's assume your range of data (with possible "x"s) is in D3:EC3 then use this formula in conditional formatting


because it offsets each range by 1 it will only give a positive count when you have a run of at least 4 consecutive "x"s in that range. A positive number triggers the conditional formatting


You will need to use a formula in the conditional formatting to check the cells are all containing X (assuming that is the value you are checking for).

To check more than one cell use the AND function which looks to see if more than one logical criteria are met. If so it returns TRUE. Syntax for this function: AND(logical1,logical2, ...)

To add the rule, do the following;

  1. Select cell C3.
  2. Choose NEW RULE from the Conditional Formatting menu.
  3. Choose "Use formula to determine which cells to format".
  4. In the box for "Format values where this formula is true:" enter the formula needed. (I've given an example below).
  5. Click the Format... button and select the formatting you desire.
  6. Click OK twice to save it.
  7. Test it by entering "X" in the cells.

enter image description here

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.