Hi Nik

See if this is what you are looking for. It contains a macro, so make you enable macros.

It will generate random colours whenever a cell in column A changes.

Sunny

Hi SunnyKow

Thanks for responding.

I have pasted my data into sheet 1 but nothing has happened to change the colours.

There is code in Sheet1 but there are no modules with macros to run.

Any idea what I am doing wrong?

Cheers
Nik

I first put in some numbers in column A and after each number there came various colored lines on the row I was working on. So that's good.

When I copied and pasted some data from another sheet at first nothing happened.

But after changing the number in column A there came another color on that row, exactly what you asked for I think.

I also can see the Macro (with Alt-F11) Sunny made.

So maybe try again? As far as I see it's working fine.

Thanks, Franz.

I have probably not explained myself very well.

I have attached an example...

So where the reference changes in column A I'd like a new colour for the next reference.

2 colours are fine. I only want to break the groups up and make them easier to view.

Also, the data is already in a spreadsheet. The colouring needs to be applied retrospectively to the data.

Cheers
Nik

Hi Nik

The macro is in the Sheet1 module. It uses a worksheet change event to trigger the macro.

It is set to run only if one cell in column A is changed, not multiple cells simultaneously.

This is to prevent you from pasting an entire column and triggering over a million colour changes.

When you paste for the 1st time nothing will happen. Subsequent changes to any cell in column A will then trigger the macro.

Hope I explained it clearly.

Sunny

Thanks, Sunny. But your solution doesn't do what I need.
I already have the data in a spreadsheet. What I need is to retrospectively apply 2 colours to make reading the data easier.
I thought I had seen it done with a =MOD formula in conditional formatting, but I can't seem to find it again.

Any other ideas?

Just in case someone else needs something similar, I have a solution...

Inserting a helper column B then copy down the following formula "=IF(A2=A1,B1,NOT(N(B1)))"

This returns a TRUE or FALSE value. I was then able to filter on one result and apply the colours.

Thanks for your help.
Keep up the good work, sharing the knowledge 🙂

Hi Nik

Maybe I misunderstood your question as you did mention

"I'd like to colour the rows based on when the value in column A changes."

If you just wanted banded rows then you can convert your data to an Excel Table. It will give you the highlight you wanted.

The formula you mentioned used conditional formatting and it uses a formula to determine whether the row number is odd or even and colour the rows accordingly. You can use ISODD(ROW()) or MOD(ROW(),2)=0 as it will return TRUE/FALSE.

ISODD wasn't available in Excel 2003 so MOD() was used instead. Both will give the same result.

Converting the range to an Excel Table is the best as the banding auto adjust when you add/delete rows.

Cheers

Sunny

I believe Nik needs a way to format blocks of data, not just alternating rows, they are alternating clusters of data.

" when the value in column A changes" : The value from column A may not change at each row, only when there are no duplicates in that column.

The attached example should work, the formula for the helper column is simple:

=IF(A2=A1,D1,IF(D1=1,0,1))

Hi Sunny

Thank you for responding and providing the additional information.
Apologies, I didn't explain myself well, but as per the example data I posted, there could be up to 5 rows before the value in column A changed, therefore I could not use the "format as table" option.

Thanks, Catalin. I will keep a hold of that formula for next time 🙂

Cheers
Nik

Hi Nik

My apologies, I totally misunderstood your request .Thanks Catalin for clarifying it.

Catalin's example is the easiest to implement but if you need a macro, then see the attachment.

Hope I got it right this time.

Cheers

Sunny

Hey, Sunny. That was spot one.

Your code is awesome and does exactly what I need.

Many thanks 🙂
Nik