VBA - Need help formatting a row range based on string or format in column A

[Matt__S]

I have the code below that works. It searches column A looking for "00" as the left most characters and then formats that cell with white text and black interior.

Dim cel As Range
For Each cel In Range("A6:A300")
If Left(cel.Value, 2) = "00" Then
cel.Font.Color = vbWhite
cel.Interior.Color = vbBlack
End If
Next

What I need to happen is for it to not only format the cell in Column A but then to copy that same format from column A to column W. Ideally I need it to search column for "00" as the left most characters in column A and then it applies the formatting above from column A to column W. This is an often updated report and where "00" appears varies each time the data is pulled.

I've gotten this code to "work" using entirerow but I do not want the "entirerow" formatted in this way.

Appreciate the help! Thanks!

 

[JackDanIce]

Welcome to the board. Try:

Dim x   As Long
Dim LR  As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For x = 6 To LR
    With Cells(x, 1).Resize(, 23)
        If Left$(.Cells(1, 1).Value, 2) = "00" Then
            .Font.Color = vbWhite
            .Interior.Color = vbBlack
        End If
    End With
Next x

Application.ScreenUpdating = True

 

[Matt__S]

Welcome to the board. Try:

Dim x   As Long
Dim LR  As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For x = 6 To LR
    With Cells(x, 1).Resize(, 23)
        If Left$(.Cells(1, 1).Value, 2) = "00" Then
            .Font.Color = vbWhite
            .Interior.Color = vbBlack
        End If
    End With
Next x

Application.ScreenUpdating = True

Thank you so much! Worked perfectly!

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option that you may consider is to do them all at once rather than row-by-row along these lines.

Sub Format_00_Cells()
  With Range("A5:W" & Range("A" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=1, Criteria1:="00*"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
      With .Offset(1).Resize(.Rows.Count - 1)
        .Font.Color = vbWhite
        .Interior.Color = vbBlack
      End With
    End If
    .AutoFilter Field:=1
  End With
End Sub

 

[JackDanIce]

You're welcome and I'd recommend Peter_SSs as a faster solution especially when your data size is large