Macro Multiple Find and Replace with a formatted cell in Excel 2010

**isasa74** · 08-23-2012, 08:55 AM

Hi Guys,

Thanks for the support provided by your forum that I've just joined

I'm using with good profit a macro that replace value in column A if found in column C with a value in column B.

Now I would need to have the destination cell for the replaced value be formatted with green fill.

Macro is the one below, could you please suggest how to add the green fill format for the replaced values?

PHP Code:


Sub ReplaceText() 



For Each vCell In Range("A2:A200").Cells 



Columns("C:C").Cells.Replace What:=vCell, Replacement:=vCell.Offset(0, 1).Value, LookAt:=xlWhole, _ 

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 

        ReplaceFormat:=False 



Next vCell

End Sub

Many thanks in advance!

**JOHN H. DAVIS** · 08-23-2012, 10:36 AM

Maybe?

Please Login or Register  to view this content.

**isasa74** · 09-17-2012, 12:56 PM

hi thanks for the reply,

I think what you are seuggesting must be added to my macro, true?
where exactly to insert this addictional part of the code ?
Please be patient i'm totally new of macros
Thanks again

**JOHN H. DAVIS** · 09-17-2012, 01:01 PM

It's been awhile since I wrote this. However, I think I was suggesting a replacement code, for what you had. Try it as a replacement first too see if it does what you want.

**isasa74** · 01-30-2014, 08:27 AM

Hi John,

sorry for the long pause but I'm just now going back to this job and I would need your code again.

No it doesn't...actually it replaces values in column "A" instead of "C"...and it entirely formats the column "A".

Let's stick to the fist code that actually worked like a charm!!
what I need is that in column "C" the replaced value to be somehow highlighted please (no matter if by red fonts or green interiour!)

THANKS!

**JOHN H. DAVIS** · 01-30-2014, 10:45 AM

Try:

Please Login or Register  to view this content.

This code will work as well as the first. But it's a matter of understanding what you require. Your statement:

"I'm using with good profit a macro that replace value in column A if found in column C with a value in column B."

is not clear of what you require. I just realized that this thread is over a year old?

**isasa74** · 01-30-2014, 11:18 AM

yes the thread is very old as I said I'm sorry about that...

the fist code ï posted did exactly what I needed :

Please Login or Register  to view this content.

it means, Macro searches Colomun C for values in common with Column A and if those are found they are replaced with the corrispondent value in B.
in the above example macro finds value "X" in C and replaces it with the corrispondent value in B: X1

All I need is the replaced values in C to be highlited so I can easily find them in a long list.

I hope I have been clearer now and super thanks for your help

**JOHN H. DAVIS** · 01-30-2014, 11:39 AM

Did you test the code in Post #6? In your after example should row 2 Column C be K1? Or just K?

**isasa74** · 01-31-2014, 05:09 AM

K1 because, macro found K in Column C and replaced it with K1 as wanted.

code in post 6 doesn't work properly infact only row 1 and 4 were replaced, while there were much more expected to be.
Please consider that columns A&B can be much shorter then C but still the replacement must cover all C values if need be :

PHP Code:


Before code in post 6

1    1A    1
1    1A    1
3    3A    2
2    2A    3
4    4A    4
           4
           4
           7
           3
           1

PHP Code:


 After code in post 6,please note that the replaced value were actualy highlighted in green and that is very good!

1    1A    1   Skipped
1    1A    1A  Correct
3    3A    2   (this should have been 2A according to row for in colum B, doesn't matter if the value"2" has been found in a different row.)  
2    2A    3   (this should have been 3A according to row for in colum B, doesn't matter if the value"3" has been found in a different row.)
4    4A    4A  Correct
           4   WRONG macro must cover all colomun C always referring to what we have in column A&B please, shoudl have been 4A
           4   same as above
           7   correct but only because macro didn't take it into any account
           3   WRONG macro must cover all colomun C always referring to what we have in column A&B please, shoudl have been 3A
           1   same as above it should have been 1A according to row 1 in coloumn A&B please.[/COLOR]

Jhon thanks again

, but in my opinion we shoudl try to use the code I posted and starting from that one adding the only formatting command to highlight the reaplced value in C.

THANKS!