Colour rows based on a cells value | General Excel Questions & Answers

Colour rows based on a cells value

Nik Alexander

Member

Members

Forum Posts: 7

Member Since:
April 30, 2014

Offline

March 16, 2017 - 2:56 am

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

See Answer

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

March 16, 2017 - 9:37 am

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

Nik Alexander

Member

Members

Forum Posts: 7

Member Since:
April 30, 2014

Offline

March 16, 2017 - 10:02 pm

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

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

March 16, 2017 - 11:21 pm

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.

Nik Alexander

Member

Members

Forum Posts: 7

Member Since:
April 30, 2014

Offline

March 17, 2017 - 1:28 am

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

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

March 17, 2017 - 9:29 am

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

Nik Alexander

Member

Members

Forum Posts: 7

Member Since:
April 30, 2014

Offline

March 17, 2017 - 7:18 pm

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?

Nik Alexander

Member

Members

Forum Posts: 7

Member Since:
April 30, 2014

Offline

March 17, 2017 - 9:21 pm

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 🙂

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

March 18, 2017 - 9:37 am

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

March 18, 2017 - 4:09 pm

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))

Nik Alexander

Member

Members

Forum Posts: 7

Member Since:
April 30, 2014

Offline

March 20, 2017 - 10:34 pm

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

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

March 22, 2017 - 1:13 pm

Hi Nik

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