Apply function/code to every row in a worksheet using VBA

Question

I have a macro which applies conditional formatting to a row which highlights specific values I was wondering if there was a way to apply it to multiple rows (I want it to run from rows 18-79) As you can see from my code below, the function has to be adjusted for each row individually (I have done 3). I was wondering if there was an easier way to apply this rather than repeat and adjust for all the rows I need.

Sub Highlight()
'
' Highlight good values

Application.ScreenUpdating = False

    Dim ws As Worksheet


    For Each ws In ActiveWorkbook.Worksheets

    ws.Activate


    Rows("18:18").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C$18", Formula2:="=$D$18"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Rows("19:19").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C$19", Formula2:="=$D$19"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Rows("20:20").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C$20", Formula2:="=$D$20"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Next ws

Application.ScreenUpdating = True

End Sub

Scott Craner · Accepted Answer · 2020-02-04 22:59:08Z

1

change the range to include it all:

With ActiveSheet.Rows("18:79")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C18", Formula2:="=$D18"
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

answered Feb 4, 2020 at 22:59

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zacchini Over a year ago

Each row has its own individual Formula e.g. for row 18 Formula1:="=$C18", Formula2:="=$D18" and for row 19 Formula1:="=$C19", Formula2:="=$D19"

Scott Craner Over a year ago

@Zacchini did you try it, Conditional Formatting will iterate the relative references.

Zacchini Over a year ago

Ahh yes it works. I forgot to add end sub hence wasn't running

Collectives™ on Stack Overflow

Apply function/code to every row in a worksheet using VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related