Conditional formatting

[mahmed1]

Hi Guys,

How do i conditionally format each time there is a change.

i.e

Mark
Mark
Mark
Mark
Mark
Mark
John
John
John
John
John
Andrew
Andrew
Andrew
Andrew

so for mark have it green and when it changes to John have it red but when it changes back to Andrew have it green again etc...

Many Thanks

 

[grizz]

assuming your cells start A1 highlight your cells --- conditional format
1ST ---cell value is / equal to "Mark" format colour
2ND --- formula / = A1="John" format colour
3RD --- formula / =A1="andrew" format colour

 

[mahmed1]

assuming your cells start A1 highlight your cells --- conditional format
1ST ---cell value is / equal to "Mark" format colour
2ND --- formula / = A1="John" format colour
3RD --- formula / =A1="andrew" format colour

Hi the problem with that is that it could be any value so therefore it will take forever to ="xxxx".

I need to have the cell colours change when the value changes.

Thank you

 

[grizz]

you have me a little confused
do you want any change in your worksheet to change colour or just the 3 names you have shown

 

[mikerickson]

Try putting this CF condition on A2 and copying it downward

=(A1<>A2)

 

[Marcelo Branco]

Hi,

Maybe using a helper column, say, column X

Put in X1
1

Formula in X2 (assuming names in column A beginning in A1)
=IF(A2=A1,IF(X1=1,1,0),IF(X1=1,0,1))

Select A1:A? (A1:A1000 for example)
Then you can CF
1st rule
$X1 = 1 ---> Green

2nd rule
$X1 = 0 ---> Red

HTH

M.

 

[Marcelo Branco]

oops...

1st rule
=AND($X1<>"",$X1=1)
Green

2nd rule
=AND($X1<>"",$X1=0)
Red

M.

 

[mahmed1]

Try putting this CF condition on A2 and copying it downward

=(A1<>A2)

Thank you, i need to highlight all the cells one colour and when the value changes have it a different colour (all cells different colour) etc.....

 

[mikerickson]

For conditional formatting you could use three condtitions like

=(MOD(COUNTIF(A:A,A1), 3)=0)
=(MOD(COUNTIF(A:A,A1), 3)=1)
=(MOD(COUNTIF(A:A,A1), 3)=2)

to give each block a different color. Some blocks would have the same color, but not neighboring blocks.