Conditional Formatting: Shade rows with alternate text

[omairhe]

Hey all,

Hope you're doing well. Could someone kindly help me out with conditional formatting formula to achieve highlighting each row with alternate data in column A.


for instance

From this:-

Column A	Row Color
abc	White
abc	White
abc	White
abc	White
ab	White
ab	White
ab	White
ab	White
ax	White
ax	White
aj	White
ant	White
ant	White
ant	White

<colgroup><col><col></colgroup><tbody>
</tbody>

To This:-

Column A	Row Color
abc	Yellow
abc	Yellow
abc	Yellow
abc	Yellow
ab	White
ab	White
ab	White
ab	White
ax	Yellow
ax	Yellow
aj	White
ant	Yellow
ant	Yellow
ant	Yellow

<colgroup><col><col></colgroup><tbody>
</tbody>

Will appreciate it .

Thx.

 

[Joe4]

Assuming that your data starts in A1, highlight your range, and apply this Conditional Formatting formula (and select your yellow highlight color):

=ISODD(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1&"")))

 

[omairhe]

Thank you ....

assuming that your data starts in a1, highlight your range, and apply this conditional formatting formula (and select your yellow highlight color):

=isodd(sumproduct(($a$1:$a1<>"")/countif($a$1:$a1,$a$1:$a1&"")))

 

[omairhe]

Oh no, My i3 laptop is running slow. Could there be a robust formula? My range is A1:Z15000

My rows will not have similar data in more than 40 rows max. After maximum 40 rows the data will change in column A for sure whereas in some other instances after the first row the data may change in the second row..

 

[Joe4]

What you want to do requires these intensive formulas or array formulas (which I don't think work well in Conditional Formatting).

Other options include:

1. Using a "helper" column.
Let's say it is column C. In cell C1, enter a 1.
Then in cell C2, enter this formula and copy down for all rows:

=IF(C2=C1,C1,C1+1)

Then you can use the Conditional Formatting formula of:

=ISODD($C1)

2. Create some VBA code to do it. The easiest way would not be automated though. You would need to run the code when desired.

 

[omairhe]

.... Option 1 has worked flawlessly... "=IF(A2=A1,C1,C1+1)"

TY

 

[Joe4]

You are welcome!