Formula to change a cell color..

[salsa79]

Hello you all,

I wonder if any of you guys can help me with the following.

I have an excel file with columns A and B with text.

Column B has the same text as A, but can have corrections. Instead of using the tracking feature (as in Word), I would need a formula that highlights the cell whenever text on cell B is different from the one in cell A.

Can someone help please?

Much appreciated,
Claudia

 

[Peter_SSs]

Welcome to the MrExcel board!

To highlight the 'different' column B values, you would use Excel's Conditional Formatting feature.
On column B, you would use a Conditional Formatting formula like
=NOT(EXACT(A1,B1))
If you need more detailed help with that, tell us what version of Excel you are using.

Alternatively, in cell C1 you could put this formula and copy down:
=IF(EXACT(A1,B1),"","x")
That would put an "x" in column C of any row where the values are not identical.

 

[salsa79]

Welcome to the MrExcel board!

To highlight the 'different' column B values, you would use Excel's Conditional Formatting feature.
On column B, you would use a Conditional Formatting formula like
=NOT(EXACT(A1,B1))
If you need more detailed help with that, tell us what version of Excel you are using.

Alternatively, in cell C1 you could put this formula and copy down:
=IF(EXACT(A1,B1),"","x")
That would put an "x" in column C of any row where the values are not identical.

Hi Peter,

Many thanks for your warm welcoming and for helping me. I'm using excel 2003. How do I use the formula to change the cell color without using Format - Conditional formating menu? Or is it better to use this command?
Thanks again

 

[Peter_SSs]

How do I use the formula to change the cell color without using Format - Conditional formating menu? Or is it better to use this command?
Thanks again

You cannot use a formula to change the colour of a cell without using Conditional Formatting.

Try this. Suppose you data is in A2:B10.

1. Select B2:B10
2. Format|Conditional Formatting...|Condituion 1|Formula Is| =NOT(EXACT(A2,B2))|Format...|Patterns tab|select a colour|OK|OK

Is there some special reason for wanting to avoid Conditional Formatting?

 

[salsa79]

You cannot use a formula to change the colour of a cell without using Conditional Formatting.

Try this. Suppose you data is in A2:B10.

1. Select B2:B10
2. Format|Conditional Formatting...|Condituion 1|Formula Is| =NOT(EXACT(A2,B2))|Format...|Patterns tab|select a colour|OK|OK

Is there some special reason for wanting to avoid Conditional Formatting?

Not at all Peter, it was just a question, I'm not the expert here Let me try this, many, many thanks. Be right back...

 

[salsa79]

Peter,

Actually the formula is with ";" and not "," :

=NOT(EXACT(A2;B2))


I've manged to find this out

I'm really glad, your help has saved me lots a time!!

 

[Peter_SSs]

Actually the formula is with ";" and not "," :

=NOT(EXACT(A2;B2))

That indicates that you are using a version of Excel that is not a standard English version, as mine is.

Of course, there is nothing wrong with that but you will need to be aware of possible differeneces in language versions when asking questions in this forum since the vast majority of users here do use a standard English version.

Anyway, I'm glad you've got a solution that is working for you.

 

[salsa79]

Hi Peter,

I didn't know about that
But by standard you mean UK or US? Actually my Excel is in English from United States, but maybe the country settings (Portugal) also have something to do with it, don't know.

Thanks again,

 

[Peter_SSs]

.. but maybe the country settings (Portugal) also have something to do with it

Yes, that is likely it.

 

[salsa79]

Yep