Conditional formatting on Consecutive cells
 

Help! :confused:

I’m looking to create a conditional format on a cell which states an
agent’s name that will flag Red! Sounds easy yes! however I need it to
flag red only if they have three consecutive values in different
columns but in the same row i.e in cell a2 value =below, in cell b2
value=below and in cell c2 value = below. If the sequence went below,
above, below then don’t flag red.

Can this be done?


--
welshobit
------------------------------------------------------------------------
welshobit's Profile: http://www.excelforum.com/member.php...o&userid=35589
View this thread: http://www.excelforum.com/showthread...hreadid=553538

Conditional formatting on Consecutive cells
 

Do you mean?

Highlight required column then in Conditional Formatting:

Formula is:

=AND(A1=value,B1=value,C1=value,A1<"",B1<"",C1< "")

Set colour format

Click OK

"welshobit" wrote:


Help! :confused:

Im looking to create a conditional format on a cell which states an
agents name that will flag Red! Sounds easy yes! however I need it to
flag red only if they have three consecutive values in different
columns but in the same row i.e in cell a2 value =below, in cell b2
value=below and in cell c2 value = below. If the sequence went below,
above, below then dont flag red.

Can this be done?


--
welshobit
------------------------------------------------------------------------
welshobit's Profile: http://www.excelforum.com/member.php...o&userid=35589
View this thread: http://www.excelforum.com/showthread...hreadid=553538

Conditional formatting on Consecutive cells
 

Is it just those three columns, if so

=COUNTIF(A2:C2,"value")=3

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"welshobit" wrote
in message ...

Help! :confused:

I’m looking to create a conditional format on a cell which states an
agent’s name that will flag Red! Sounds easy yes! however I need it to
flag red only if they have three consecutive values in different
columns but in the same row i.e in cell a2 value =below, in cell b2
value=below and in cell c2 value = below. If the sequence went below,
above, below then don’t flag red.

Can this be done?


--
welshobit
------------------------------------------------------------------------
welshobit's Profile:
http://www.excelforum.com/member.php...o&userid=35589
View this thread: http://www.excelforum.com/showthread...hreadid=553538

Conditional formatting on Consecutive cells
 

Thanks both,:)

Ok both work in their own right great so thanking you kindly however,
lets say the example i gave earlier was based on an agents productivity
score based over a 12 week period and within that period they score a
below over 3 consecutive weeks say at weeks 5,6,7 how would it work
then?


--
welshobit
------------------------------------------------------------------------
welshobit's Profile: http://www.excelforum.com/member.php...o&userid=35589
View this thread: http://www.excelforum.com/showthread...hreadid=553538

Conditional formatting on Consecutive cells
 

A (not very elegant) possible solution:

Assumes the 12 values are in columns B to M.

In a spare column put this formula and copy down:

=IF(ISERROR(MATCH("below",$B2:$M2,0)),"",IF(AND(OF FSET($B2,0,MATCH("below",$B2:$M2,0))="below",OFFSE T($B2,0,MATCH("below",$B2:$M2,0)+1)="below"),1,0))

If three consecutive values of "below" are found, it puts 1 in the column or
if less than 3, then 0. If there are no "below" ratings it is left blank.

You could then set your conditional formatting based on the value 1 in this
column.

HTH

"welshobit" wrote:


Thanks both,:)

Ok both work in their own right great so thanking you kindly however,
lets say the example i gave earlier was based on an agents productivity
score based over a 12 week period and within that period they score a
below over 3 consecutive weeks say at weeks 5,6,7 how would it work
then?


--
welshobit
------------------------------------------------------------------------
welshobit's Profile: http://www.excelforum.com/member.php...o&userid=35589
View this thread: http://www.excelforum.com/showthread...hreadid=553538