Conditional Formatting: Shade rows with alternate text

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,964
Office Version
  1. 2019
Platform
  1. Windows
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 ARow Color
abcWhite
abcWhite
abcWhite
abcWhite
abWhite
abWhite
abWhite
abWhite
axWhite
axWhite
ajWhite
antWhite
antWhite
antWhite

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


To This:-

Column ARow Color
abcYellow
abcYellow
abcYellow
abcYellow
abWhite
abWhite
abWhite
abWhite
axYellow
axYellow
ajWhite
antYellow
antYellow
antYellow

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


Will appreciate it .

Thx.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2009
Messages
1,964
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 26, 2009
Messages
1,964
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 26, 2009
Messages
1,964
Office Version
  1. 2019
Platform
  1. Windows
.... Option 1 has worked flawlessly... "=IF(A2=A1,C1,C1+1)"

TY
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
You are welcome!:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,123
Messages
5,599,834
Members
414,341
Latest member
Mohammedsobhey

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top