Macro to Change Header Based on Column Contents

[Michael151]

Hello all,

Trying to write a macro that will change the header (row 1) of a column based on the contents of that column.

I have a column called “Medium Type” – this column can be in any column letter, which is why I’d like to use the header in row 1 to identify.

I have four separate formats that can be included in this column: Format1, Format2, Format3, and Format4. They will never be mixed together (only 1 format per sheet).

What I’d like is for the macro to identify which format is contained in the “Medium Type” column and change the header in row 1 to that format.

For example:

Before:

Medium Type
Format1
Format1
Format1

After:

Format1
Format1
Format1
Format1

In my example above, the header in row 1 is highlighted in blue. Any help is appreciated! Thank you!

 

[Michael151]

It would be something like:

Find: "Medium Type" in row 1

Look down column containing "Medium Type"

Find either Format1, Format2, Format3 or Format4

Change header in row 1 from "Medium Type" to Format Type Found