• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight three consecutive identical values in a row.

M

MikeS

New Member
I am using a spreadsheet based on the "Employee Absence schedule" template. Sample attached.

I want to highlight, to make obvious to the user, when three consecutive absences occur.
That is, when the letter a is entered into three consecutive cells in one row.

I am using a conditional formula, "use a formula to determine which cells to format" & a format of black bold text on a yellow fill
[note: the sample has other conditions already built into the template]
I did try counting to either side only hoping that as each cell would have the same formula it owuld logically work. It did not. So now I have this:

In cell b4. counting 2 cells ahead.
=COUNTIF(B4:D4,"=a")>2

in c4, counting either side, and two ahead.
=COUNTIF(B4:D4,"=a")>2
=COUNTIF(c4:e4,"=a")>2

in d4, counting either side, and two ahead & two behind.
=COUNTIF(B4:D4,"=a")>2
=COUNTIF(c4:e4,"=a")>2
=COUNTIF(d4:f4,"=a")>2

In each case, where a, a, a, occurs a yellow fill and a bold text "a" should result.

BUT, I can only get a fill and bold on the FIRST cell in a sequence of three, AND I can't copy/paste/autofill the conditions across the entire row with the criteria changing (incrementing) automatically. As I want to put these conditions on each of 30 cells, and maybe 46 rows editing each is rediculous.

I suspect a VBA may be needed but I can't write one. OR perhaps someone knows a better way to do it.
Advice very welcome and thank you.
Mike S.

Sorry but the browse on the "upload a file" button can not see my Excel files. odd?
 
Your first column of data has to be Column C as you have to check the end of a 3 run condition which means the first column will be A

Then use a CF formula of:
=OR(AND(C4=D4,C4=E4),AND(C4=B4,C4=D4),AND(C4=A4,C4=B4))
 
Hui,
thank you this looks elegant. However, If I copy it across the row the column address do not increment, every cell continues to test C4 against the others.
I tried paste special merge conditional formatting. No difference.
I discovered this is because the template has cells formatted as a table.
Every cell has the same conditions so no increment is possible.

Also, your formula will quite happily apply the format to every cell regardless of values stored as long as it is identical to the other two.


I reformatted the table as a range, added two hidden columns (a&b) and used the following extension of your formula (thank you). $R$1 contains the letter a.

=OR(AND(D5=$R$1,E5=$R$1,F5=$R$1),AND(D5=$R$1,C5=$R$1,B5=$R$1),AND(C5=$R$1,D5=$R$1,E5=$R$1))

all cells ahead and behind are checked, all will change colour on a sequence of three "a" values.

Sample attached.
 

Attachments

Password please ?

I assume you want this applied to the 12 Monthly worksheets
 
You haven't applied the CF's correctly which is why it isn't working and is showing the wrong ranges

I would clear all the CF's in a worksheet

Then select D5 and apply the CF's

Then copy C5 as required
 
You must log in or register to reply here.
Back
Top