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?
Best Answer
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