Highlight cells with VBA or Conditional Formatting?

[starcraftbud]

Hello,

I would like to highlight certain cells or rows based on some complex criteria and I'm not sure how to attack the problem. Either conditional formatting or VBA would be fine, I'm just not sure how to accomplish what I need with either one.

I've attached a worksheet to go along with my explanation. I want to be able to alternately highlight data green and then yellow based on the same criteria, but alternating colors.

The attached spreadsheet shows a "before" on one tab and the "after" on a second tab of what I would like the finished product to look like. And I've only attached an example with several rows, but for my purposes, there could actually be hundreds of rows.

Everything hinges on column B. If the cell says "Diff", it will need to be highlighted. But if column A's fruit is the same as the prior column, I do not want the color to alternate. I only want the color to alternate when the fruit changes. So, if column A has the same fruit for 10 rows all with corresponding "Diff" in column B, then I need them all highlighted, but it should be the same color - yellow for example. Only on the next set of "Diff" for column B should the color change to blue and it could be one row or more, depending on if the fruits change or not.

As always, I am grateful for the assistance I receive here. Thank you very much for the help.

[Palmetto]

You can use CF with two formulas:

Condition-1 formula: =AND(OR($A5=$A4,$A5=$A6),$B5="Diff")
Condition-2 formula: =AND(OR($A5<>$A4,$A5<>$A6),$B5="Diff")

The Condition-1 formula must be the first priority.

See the "Before" sheet which uses these formula; seems to work correctly on the small bit of data.

[starcraftbud]

Thank you for the quick post. That is close to what I am trying to do, but not quite.

The first issue with the file you posted is that the rows highlighted blue seem to only occur when there is one fruit in Column A. As soon as there are multiple fruits of the same type, it uses yellow. Thats not quite what I need. I would like the spreadsheet to alternate between blue and yellow based on the same criteria.

To put it another way, every time "Diff" is in column B, the row gets highlighted. If the fruits are the same consecutively, they are part of the same colored set. I want each set to alternate. I've attached a new sheet which makes a larger example for you to see.

If only one row gets highlighted, that doesn't mean its blue. Or if several are highlighted, that doesn't mean its yellow. See attached. And thanks again, that was a quick response and my original explanation and example should have been clearer.

Also feel free to manipulate data or add columns, etc. Whatever is easiest. Again, I appreciate any help, suggestions, or input.

[starcraftbud]

I was actually able to figure out a solution to my problem.

I used a combination of formulas and a Loop! Thanks for the help!

[Palmetto]

Great. Please post your complete solution, including code/example workbook so that others finding this thread will have an answer to a similar problem. Thanks.