# Conditional formatting of a cell based on value in 4 adjacent cells

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).

• 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

`=COUNTIFS(D3:DZ3,"x",E3:EA3,"x",F3:EB3,"x",G3:EC3,"x")`

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.