Conditional Formatting Colours rows

Charlie987

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am hoping to be able to iterate over a list of names where the first and last names are in different cells and format the colour of each persons set of rows eg.
Peter | Smith| 1
Peter | Smith | 2
Sam | Wright | 1
Sam | Wright | 2
Sam | Wright | 3
John | White | 1

In this example it would format the two rows for peter smith in one colour eg. Light blue
and then the three rows for Sam Wright would all be a different colour eg. white
and then the row for John White would be back to light blue again

The range will be dynamic because is it generated from another workbook.

I can quite figure out how to get it working with the names being in different columns
If any one had any thoughts it would be greatly appreciated.
Thanks!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
AB
1fftt
2ab
3ab
4ab
5cd
6cd
7ef
8ef
9
10
11
12
13
14
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B14Expression=AND($A2<>"",MOD(ROWS(UNIQUE(FILTER($A$1:$B2,$A$1:$A2<>""))),2)=0)textNO
 
Last edited:

Charlie987

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi,
Thankyou! your output looks perfect. When I tried to apply it, it seemed to only highlight or switch colours when it hits the second instance eg.
But then in my image for I,J -- that doesnt seem to be the case either. because it changes at the first instance of I,J and keeps that colour for all expect the last duplicate.
Not sure if I am just applying it incorrectly. I am just adding it as a "formula to determine cells" rule. Is that correct?
Thanks again!
 

Attachments

  • Untitled.png
    Untitled.png
    13.2 KB · Views: 3

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows
Your problem most likely relates to what row your data starts on and what you had selected when you applied the CF.

For this type of problem I like to use a helper column because the CF formula couldn't be simpler & the helper column formula is more readily available to view/edit if required.
If you want, the helper column can be hidden once formulas have been entered.

To apply the CF for my layout, select from A2 down to B13 then apply the CF shown (yes, "formula to determine ..")
The important thing is that the row number (2 in my case) in the CF formula is the row number of the Active Cell.

20 12 20.xlsm
ABCD
1fftt
2ab1
3ab1
4ab1
5cd0
6cd0
7ef1
8ef1
9gh0
100
110
12ij1
13ij1
CF Groups
Cell Formulas
RangeFormula
D2:D13D2=IF(A2&B2="",0,IF(A2&"|"&B2=A1&"|"&B1,D1,1-D1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B13Expression=$D2textNO
 

Charlie987

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you! that looks great. But I am not too sure I can generate the helper column because the list of values is dynamically created from another list, so I am not really sure how to attribute those helper values to each different person. (I actually have a post from a while ago that I think you answered that shows how it is created: *Adding rows containing values based on input cell*)
Thanks
 

navic

Active Member
Joined
Jun 14, 2015
Messages
296
Office Version
  1. 2013
Platform
  1. Windows
@Peter_SSs
Peter great idea and solution

@Charlie987
But I am not too sure I can generate the helper column because the list of values is dynamically created from another list, so I am not really sure how to attribute those helper values to each different person.
You can place this auxiliary column somewhere in the last column (eg 'OLM' column). I guess you don’t use a lot of columns.

Another way
As helper 'OLM' column (this column you can hide) .
In 'OLM4' cell put this formula below
Code:
=IF(AND(A4=A3,B4=B3),OLM3,OLM3+2)-1
In 'OLM5' cell put this formula below
Code:
=IF(AND(A5=A4,B5=B4),OLM4,OLM4)
In 'OLM6' cell put this formula below (copy down to last row)
Code:
=IF(AND(A6=A5,B6=B5),OLM5,OLM5+1)
In 'CF' put this formula below
Code:
=AND(LEN($A4)>0,MOD($OLM4,2)=0)
 

Attachments

  • cfolmcolumn.png
    cfolmcolumn.png
    15.8 KB · Views: 6

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I am not really sure how to attribute those helper values to each different person.
Perhaps I am not understanding what you are saying but you are not attributing helper values to any 'person'. You would just enter the formulas in the helper column and copy down as far as you ever need. Also apply the CF in columns A:B as far as you would ever need. When values are entered into columns A:B the colour bands will appear.
 

Charlie987

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Perhaps I am not understanding what you are saying but you are not attributing helper values to any 'person'. You would just enter the formulas in the helper column and copy down as far as you ever need. Also apply the CF in columns A:B as far as you would ever need. When values are entered into columns A:B the colour bands will appear.
Ohh I see now. Yes that works perfectly. Thank you very much!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,972
Members
416,002
Latest member
Neshx

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