# Conditional Formatting: Shade rows with alternate text

#### omairhe

##### Well-known Member
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Joe4

Assuming that your data starts in A1, highlight your range, and apply this Conditional Formatting formula (and select your yellow highlight color):
Code:
``=ISODD(SUMPRODUCT((\$A\$1:\$A1<>"")/COUNTIF(\$A\$1:\$A1,\$A\$1:\$A1&"")))``

#### omairhe

##### Well-known Member
Thank you ....

assuming that your data starts in a1, highlight your range, and apply this conditional formatting formula (and select your yellow highlight color):
Code:
``=isodd(sumproduct((\$a\$1:\$a1<>"")/countif(\$a\$1:\$a1,\$a\$1:\$a1&"")))``

#### omairhe

##### Well-known Member
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:
Code:
``=IF(C2=C1,C1,C1+1)``
Then you can use the Conditional Formatting formula of:
Code:
``=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

##### Well-known Member
.... Option 1 has worked flawlessly... "=IF(A2=A1,C1,C1+1)"

TY

#### Joe4

You are welcome!

Replies
5
Views
420
Replies
1
Views
540
Replies
1
Views
348
Replies
1
Views
347
Replies
4
Views
561

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,409
Messages
5,831,452
Members
430,069
Latest member
bubbleboom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back