Change Font/Color of Cells in a Particular Column

[Michael151]

Hello all,

Trying to write a macro that will change the font and color of specific text in a particular column.

I have a column with the header (row1) "deal_id" which can appear in column letter in the sheet. I'd like to use the header in row 1 as the identifier for this column.

Within that column, if the word "OPEN" is found in a cell, change the font to bold and the color to red of that cell.

Help is most appreciated - thanks!

 

[JackDanIce]

Try:

Sub TryMe ()
Application.ScreenUpdating = False
Dim myHeader As String
Dim FoundColumn As Long, i As Long
If Len(Range("A1")) > 0 Then
  myHeader = Range("A1")
Else
  MsgBox "No header found, macro exiting"
  Exit Sub
End If
On Error GoTo Notfound
FoundColumn = Range(Cells(1, 2), Cells(1, Columns.Count)).Find(what:=myHeader, searchorder:=xlByColumns).Column
i = Cells(Rows.Count, FoundColumn).End(xlUp).Row
Do While i <> 1
    With Cells(i, FoundColumn)
        If .Value = "OPEN" Then
            .Font.Bold = True
            .Interior.ColorIndex = 3
        End If
    End With
    i = i - 1
Loop
Application.ScreenUpdating = True
Exit Sub
Notfound:
MsgBox "Header can't be found, macro exiting"
End Sub

 

[Michael151]

Thanks JackDanIce,

However, I get an error message saying "object variable not set" and highlights this line:

FoundColumn = Range(Cells(1, 2), Cells(1, Columns.Count)).Find(what:=myHeader, searchorder:=xlByColumns).Column

 

[Michael151]

Also, wondering where "deal_id" appears as something for the macro to find in the row 1 header - didn't see it included in the code.

 

[JackDanIce]

Your query wasn't clear so I assumed you were looking for whatever header was in cell A1 and then highlighting as required. The line you comment on works fine for me, but try this instead:

Sub TryMe ()
Application.ScreenUpdating = False
Dim FoundColumn As Long, i As Long
On Error GoTo Notfound
Range(Cells(1, 1), Cells(1, Columns.Count)).Find(what:="deal_id", searchorder:=xlByColumns).Select
FoundColumn = ActiveCell.Column
i = Cells(Rows.Count, FoundColumn).End(xlUp).Row
Do While i <> 1
    With Cells(i, FoundColumn)
        If .Value = "OPEN" Then
            .Font.Bold = True
            .Interior.ColorIndex = 3
        End If
    End With
    i = i - 1
Loop
Application.ScreenUpdating = True
Exit Sub
Notfound:
MsgBox "Header can't be found, macro exiting"
End Sub

F8 (step) through the code and you should see the column with deal_id selected first..

 

[Michael151]

Thanks JackDanIce, that worked!