How to set conditional formatting for each change of value in column C

F

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
328
Office Version
  1. 365
Platform
  1. Windows
Hi to all,

I'm trying without success to set a conditional formatting for the row when a change happens in column C adding blue background. Is possible without using a helper column.

The input range is like this:

Book1
ABCDEF
1IDPLXMDLSZNUM
201WERPDIAT775
311SOLUPLAT802
421SOLUPLAT977
531THREDIAT883
641USYTPLAS220
751YLKJDIAT802
861YLKJDIAT509
971CHREPLAS605
1081CHREPLAS967
1191CHREPLAS520
12101CHREPLAS255
13111CHREPLAS167
14121KQSADIAT268
15131KQSADIAT849
16141KQSADIAT191
17151KQSADIAT244
18161KQSADIAT273
19171KQSADIAT939
Sheet1


The output I'm looking for is like this:

1666761887897.png


This is my attempt so far
Book1
ABCDEF
1IDPLXMDLSZNUM
201WERPDIAT775
311SOLUPLAT802
421SOLUPLAT977
531THREDIAT883
641USYTPLAS220
751YLKJDIAT802
861YLKJDIAT509
971CHREPLAS605
1081CHREPLAS967
1191CHREPLAS520
12101CHREPLAS255
13111CHREPLAS167
14121KQSADIAT268
15131KQSADIAT849
16141KQSADIAT191
17151KQSADIAT244
18161KQSADIAT273
19171KQSADIAT939
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E19Expression=AND(C5<>C6,C5=C4)textNO


Thanks for any help.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Hello,

see below

Book1
ABCDEF
1IDPLXMDLSZNUM
201WERPDIAT775
311SOLUPLAT802
421SOLUPLAT977
531THREDIAT883
641USYTPLAS220
751YLKJDIAT802
861YLKJDIAT509
971CHREPLAS605
1081CHREPLAS967
1191CHREPLAS520
12101CHREPLAS255
13111CHREPLAS167
14121KQSADIAT268
15131KQSADIAT849
16141KQSADIAT191
17151KQSADIAT244
18161KQSADIAT273
19171KQSADIAT939
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F19Expression=$C1<>$C2textNO
 

Attachments

  • 1666763513686.png
    1666763513686.png
    19.7 KB · Views: 6
Upvote 0
hrayani said:
Hello,

see below...
Click to expand...
Thank for your help. It works! I see it was easies than I was trying to.

Not sure if you can help me with the final question. Can be grouped all rows that have the same value in C, alternating blue and blank background like below?

Book1
ABCDEF
1IDPLXMDLSZNUM
201WERPDIAT775
311SOLUPLAT802
421SOLUPLAT977
531THREDIAT883
641USYTPLAS220
751YLKJDIAT802
861YLKJDIAT509
971CHREPLAS605
1081CHREPLAS967
1191CHREPLAS520
12101CHREPLAS255
13111CHREPLAS167
14121KQSADIAT268
15131KQSADIAT849
16141KQSADIAT191
17151KQSADIAT244
18161KQSADIAT273
19171KQSADIAT939
Sheet3
 
Upvote 0
Fractalis said:
Thank for your help. It works! I see it was easies than I was trying to.

Not sure if you can help me with the final question. Can be grouped all rows that have the same value in C, alternating blue and blank background like below?
Click to expand...
You're welcome

pls see below

Book2
ABCDEF
1IDPLXMDLSZNUM
201WERPDIAT775
311SOLUPLAT802
421SOLUPLAT977
531THREDIAT883
641USYTPLAS220
751YLKJDIAT802
861YLKJDIAT509
971CHREPLAS605
1081CHREPLAS967
1191CHREPLAS520
12101CHREPLAS255
13111CHREPLAS167
14121KQSADIAT268
15131KQSADIAT849
16141KQSADIAT191
17151KQSADIAT244
18161KQSADIAT273
19171KQSADIAT939
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F19Expression=$D2="DIA"textNO
 
Upvote 0
hrayani said:
You're welcome

pls see below

Book2
ABCDEF
1IDPLXMDLSZNUM
201WERPDIAT775
311SOLUPLAT802
421SOLUPLAT977
531THREDIAT883
641USYTPLAS220
751YLKJDIAT802
861YLKJDIAT509
971CHREPLAS605
1081CHREPLAS967
1191CHREPLAS520
12101CHREPLAS255
13111CHREPLAS167
14121KQSADIAT268
15131KQSADIAT849
16141KQSADIAT191
17151KQSADIAT244
18161KQSADIAT273
19171KQSADIAT939
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F19Expression=$D2="DIA"textNO
Click to expand...
Thanks again. But in this case, the values in column D could be variable, so, the only column to consider to create a logic should be column C.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Conditional Format - Highlight Cell Based on Date
2
Replies
10
Views
201
DanteAmor
DanteAmor
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
174
StephenCrump
S
J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
128
JAZ91
J
J
  • Question Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
830
RoryA
RoryA
C
  • Question Question
Conditional Formatting for an exact formula
2
Replies
10
Views
134
Fluff
Fluff

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top