April 30, 2014
Hi guys
I have a spreadsheet and in column A there is a reference number and data up to column Z.
I'd like to colour the rows based on when the value in column A changes.
Simple alternating colours is fine. I only want to make the data easier to read.
So when the reference changes, the row colour changes to colour 2 and when the reference changes again colour 1 is applied to the row.
Is this possible?
Many thanks
Nik
VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
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.
April 30, 2014
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
VIP
Trusted Members
June 25, 2016
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
April 30, 2014
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?
April 30, 2014
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 🙂
VIP
Trusted Members
June 25, 2016
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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))
April 30, 2014
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
1 Guest(s)