Find specific phrase in a column and change the row color and its border

I have the table, as shown on left in the appended image, and the final result should be the right one.

The steps are written as 1, 2, 3 and 4.

Untitled.png

I have been trying to tackle this problem since yesterday, so some help will be very welcome.

Thank You.

Hi, I shall look at it. Can you upload sample workbook, instead of image? Also I assume varying number of rows, but always data in columns A:E?

Hi,
find attached file. I think it does what you need. I apply formatting to columns A:E, but it's easy to change if you also need column G to be gray.
Let me know if this is what you need.

Buran

Hi, use the file attached to this post. I have made a small change to make sure cells in column B are not merged.

glad that helped. I was thinking about merged cells. note that actually there should be no merged cells in columns C:E, otherwise cell formatting would fail. If there is a chance that cell will be merged - you need to change the macro to unmerge them

note that you can not have shade on part of a merged cell

1) Copy A, B, C columns from the file and paste into the template starting from A8

2) Copy Macro-v13! Column D to Template! Column I

3) Copy Macro-v13! Column E to Template! Column O

Hi,
copy/paste is simple. But there a lot of other questions. e.g.
template.xlsx will be opened and always with that name or user will have to browse for a file and open it
do you need to transfer just the values? I assume yes (i.e. keep format of the template file
is there going to be only one sheet in the template always?
that's for start. there may be other questions too

Hi,
find attached file. I fixed a bug in the formatting.
There is new macro CopyData. It will copy data from the active sheet to template.xlsx.
If it is not opened it will try to open it, looking in the same directory where the file is for a file named Template.xlsx.
It doesn't save the file after data are transferred. It exports it in the same folder as pdf. It will overwrite if there is file Template.pdf in the same folder.
In my opinion it's better to have the template.xlsx as a template file (i.e. template.xltx) or as a hidden worksheet in the current workbook. Also it may be convenient to have the macros in a add-in with nice RibbonX interface. This way macros will be independent of the file they reside in and always available.
It's late here in Europe, so we can discuss it tomorrow.