VBA to search for values ​​and change the format for Row + 2

advertisements

Can someone give me a helping hand with the following piece of vba? What I am trying to achieve is search for a string of text in a column and if it matches my criteria, change the format of a dynamic range. I am having a hard time figuring out how to make that range dynamic. It's always the rows that are matching the string + 2.

Here's what I've got so far:

Sub FormatPercentages()
Dim RowToTest As Long

For RowToTest = Cells(Rows.count, 27).End(xlUp).Row To 2 Step -1

With Cells(RowToTest, 27)
    If .Value Like "*Efficiency*" _
    Then _
    Range("AD129:AS131").NumberFormat = "0%"
End With

Next RowToTest

End Sub

I need help on the range part:Range("AD129:AS131").NumberFormat = "0%" I think it should be something like this although I am aware that it's poorly written (AD & "N" : & AD.xlToRight & "N"+2). Where N is the row number that matches the string Efficiency.

Thank you, C


Probably the confusion comes from the strange usage of _. Does it work like this?

Sub FormatPercentages()

    Dim RowToTest As Long

    For RowToTest = Cells(Rows.Count, 27).End(xlUp).Row To 2 Step -1
        With Cells(RowToTest, 27)
            If .Value Like "*Efficiency*" Then
                Range("AD" & RowToTest & ":AD" & RowToTest + 2).NumberFormat = "0%"
            End If
        End With
    Next RowToTest
End Sub

I have not tested it, but it looks like what you need, assuming that N from your question is RowToTest.