Conditional Formating based on Cell Value

[diggerdidoodar]

I want to if possible format the full row based on the cell value in field "Lev2 Matl". So first 3 rows based on Lev2 Matl=G10TSM05A would be 1 colour. Next 3 rows based on Lev2 Matl=G10TSM05M would be another colour etc etc. No issue with cycling through available colours only. Hope that makes sense.

Below is example of first two columns in my multi column spreadsheet. TW10 is column one, materials beginning with "G" is the "Lev2 Matl" column.

Lev1 Matl Lev2 Matl
TW10 G10TSM05A
TW10 G10TSM05A
TW10 G10TSM05A
TW10 G10TSM05M
TW10 G10TSM05M
TW10 G10TSM05M
TW10 G10TSM05M
TW10 G10TSM07A
TW10 G10TSM07A
TW10 G10TSM07A
TW10 G10TSM07M
TW10 G10TSM07M
TW10 G10TSM07M
TW10 G10TSM07M
TW10 G10TSM10A
TW10 G10TSM10A
TW10 G10TSM10A
TW10 G10TSM10M
TW10 G10TSM10M
TW10 G10TSM10M
TW10 G10TSM10M

 

[Special-K99]

"So first 3 rows based on Lev2 Matl=G10TSM05A would be 1 colour"

Not specific enough.
Your subject says "Conditional Formating based on Cell Value" but your descripition sounds like the subject should be "Conditional Formating when a cell value changes"

Are you saying you want to format every row that contains G10TSM05A, e.g. red, ie EVERY row that contains G10TSM05A will be red no matter where it is in the list (in which case you will need one colour/rule for EVERY different value in "Lev2 Matl") ?

Or are you saying you want to format the list when a "Lev2 Matl" value changes in which case you need to specify the number of colours you want to use ?

 

[diggerdidoodar]

"So first 3 rows based on Lev2 Matl=G10TSM05A would be 1 colour"

Not specific enough.
Your subject says "Conditional Formating based on Cell Value" but your descripition sounds like the subject should be "Conditional Formating when a cell value changes"

Are you saying you want to format every row that contains G10TSM05A, e.g. red, ie EVERY row that contains G10TSM05A will be red no matter where it is in the list (in which case you will need one colour/rule for EVERY different value in "Lev2 Matl") ?

Or are you saying you want to format the list when a "Lev2 Matl" value changes in which case you need to specify the number of colours you want to use ?

I would like to format the list when a "Lev2 Matl" value changes in ascending order. It can just be two colours just to define between the value change.

 

[diggerdidoodar]

I would like to format the list when a "Lev2 Matl" value changes in ascending order. It can just be two colours just to define between the value change.

 

[Special-K99]

Ok, everywhere Ive looked you need a Helper Column, hide it if you dont want it displayed.

With "Lev1 Matl" in A1, so your data starts in A2, do this

in C2
=IF(B2=B1,C1,C1+1)

Select your entire range, in your example A2:B22

Conditional Formatting
New Rule
Use a formula to determine...

=MOD($C2,2)=0
Format with 1st colour

If you want to alternate with another colour add an extra rule to the selection

=MOD($C2,2)=1
Format with 2nd colour