[Solved] Conditional Formatting

[MikeJP]

Hi. Can you help please?

I have a large s/sheet with many columns and rows. Two columns relate to this query.

In one column (G) I have a list of numbers which is where new numbers are entered as received; in another column (BA) I have a COUNT IF forumula that seaches Col G to look for duplicates; and if found this will show “Duplicate” in the relevant Col BA field. This works well and uses the formula =IF(COUNTIF($G$5:$G$9999;G5)>1;"Duplicate";"").

It would be helpful if the Col G field in which I type the next number, would change background colour to red if the new number entered was recognised as a duplicate from the text shown in Col BA.

Hope this makes sense. Thank you.

[erbsenzahl]

For Cell G7 you could use Conditional Formatting like this (after hitting the return key the CF shows style red):

Code: Select all

Formula is               BA7="Duplicate"
Apply style                        red

or

Code: Select all

Formula is               COUNTIF($G$5:$G$9999;G7)>1
Apply style                        red

Style "red" can be created before or while managing the CF.

You easily can spread your CF all over column G. Select one cell which contains the a.m. CF, use Clone Formatting paintbrush, type in Name Box G5:G9999 and hit return key (now selection of G5 to G9999 is to see) and use Clone Formatting paintbrush.

[MikeJP]

Thanks for your quick reply. I have followed your suggestions, but sadly it is not working. I am getting no changes showing at all; but I will keep trying. If I was allowed to attach a screen shot it would help I am sure; but this is restricted.
Any further suggestions would be appreciated.
I am sure it is something I am doing wrong.
Thanks.

[MikeJP]

Hello again.
Got it working now thank you. It was simply the CF choice of Cell Value or Formula.
I will now try to get the CF settings applied to all the 700 fields in Col G, using your suggestion of Clone Paintbrush......which I have yet to find!
Thanks.

[MikeJP]

A final reply. Thank you 'erbsenzahl' for your help; all working as wanted.
I appreciate your help; thanks, Mike.