VBA Replace text in column, if column header is a specific text

[anarchyflag]

I have a report which plots venue availability against dates (see layout below)

The information comes through as Fullday / Morning / Afternoon, and if used will say Booked.

None of the venues are used on Sundays, except to set up. However, this data still comes through as a full day availability, which messes with calculations used for reporting later down the line (can look like the venues have offered more availability than they actually have).

I have the following two pieces of code in place already:

Sub Find_replace()
    Range("A:AAA").Select
    Selection.Replace What:="Fullday", Replacement:="***Full", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End Sub

This searches for cells with “Fullday” and replaces it with “***Full” across the entire worksheet.

Sub Sunday_widths()
Dim Range1 As Range
Dim Cell1 As Range
Set Range1 = Range("O1:AAA1")
For Each Cell1 In Range1
    Select Case True
        Case Celll Like "*Sun*"
            Cell1.ColumnWidth = 7.5
    End Select
Next Cell1
End Sub

This searches for any column with “Sun” in Row 1, and sets the width of the column based on that.

Is there any way to combine these two, so that the macro searches for any column with Sun in the top row, selects that column and replaces any text “***Full” with “Set up”?

Thanks very much in advance!

 

[anarchyflag]

Actually, I've just solved it:

Sub Sunday_avails()

Dim Range1 As Range
Dim Cell1 As Range
Set Range1 = Range("O1:AAA1")
    For Each Cell1 In Range1
        Select Case True
            Case Cell1 Like "*Sun*"
                Cell1.EntireColumn.Replace What:="***Full", Replacement:="*Set up", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        End Select
    Next Cell1
End Sub