Excel Conditional Formatting for rows

0
votes

What formulas should I use in Excel as a Conditional Formatting to achieve the follow result:

  • Color the entire rows until the value in column B change.
  • Then do not color the rows until the value in column B change again.
  • Then color the entire rows until the value in column B change again.
  • And so on.

In this text example, the rows that begins with an X should be colored and O shouldn't.

X   1000    en-US   Application initialization complete.
X   1000    es-ES   Aplicación inicializada completa.
X   1000    fr-CA   Initialisation de l'application complétée.
O   1011    en-US   Save communication messages to disk operation enabled.
O   1011    es-ES   Habilitada  operación de guardar mensajes de comunicación en disco.
O   1011    fr-CA   Enregistrement des messages de communication sur disque activé.
X   1012    en-US   Save communication messages to disk operation disabled.
X   1012    es-ES   Deshabilitada  operación de guardar mensajes de comunicación en disco.
X   1012    fr-CA   Enregistrement des messages de communication sur disque désactivé.
O   1014    en-US   User session started.
O   1014    es-ES   Sesión de usuario iniciada.
O   1014    fr-CA   Session usager démarrée.
X   1015    en-US   User session ended.
X   1015    es-ES   Sesión usuario finalizada.

Thank you

2
excelformattingconditional
do you only have two possible values? – nutsch
column B changes regularly. But I only required 2 colors (or only one, the X). column B is sorted. I need something like =ROW()<>ROW()-1 – user2173894
is the X a constant value or can you have any sort of values? – nutsch
Stackover flow doesn't let me post images but the first column is dummy for my example. I do not have X or O in my actual spreadsheet, its another data in column A. I just wanted to explain that I want the 3 first lines to be colored, etc. (based on column B being a different value from the previous row). – user2173894
In other word, the formulas must only rely on column B's content. – user2173894

2 Answers

3
votes

If your data starts in row 2 (with a header row in row 1) then you can select the whole range of data, e.g. A2:D1000 and then use this formula in conditional formatting

=MOD(SUM(($B$1:$B1<>$B$2:$B2)+0),2)

...or with data starting at row 1 this formula should work for you

=MOD(ROUND(SUM(1/COUNTIF($B$1:$B1,$B$1:$B1)),0),2)

1
votes

If your trigger column follows sequence and does not skip numbers something like this would work

=ROUND(A2/2,0)*2<>A2