Blue Flower

How to Highlight each N-cell of an unbroken string in the same row

For this Excel tutorial, I have prepared an example of how to highlight the background of a cell located in a specific location, i.e. order if the data appears multiple times in an continous sequence. In the picture below, find out what our task looks like. In this situation, we have workers in a company that does a certain job. A certain job is presented with a certain text. Since working time every day, regardless of Saturday, Sunday, holidays, it is necessary to provide the worker free day after six days.

In other words, we want to visualize every sixth day after a worker is doing some repeat work. For this problem we will use Conditional Formatting.

Highlighting every six cells in continuous sequence

In the picture above, notice how the cell range is set. Since it is necessary to highlight every 6th day the range of cells to which we will apply Conditional Formatting (CF) will begin in the sixth column. So we select the cell F2 range to the last row/last column.

Clicking the 'Conditional Formatting' command on the HOME tab will set the following formula.

=AND(COUNTIF(A2:E2;F2)=5;LEN(F2)>0) (non-US settings)

or

=AND(COUNTIF(A2:E2,F2)=5,LEN(F2)>0)

Enter the formula as a Conditional Conditional Rule to highlight the cell

Attached to this Excel tutorial, you have the option to download ZIP files
(Highlight every Nth cell in the same row) in which the Workbook I used when creating this tutorial.