Excel - Loop Conditional Formatting Column by Column

Question

It seems fairly simple, at least in my head, but I don't really know how to write up the formula to do current column + 1 to repeat the same conditions over and over, as well as changing the cell so it's (Column + 1, Row + 1)

Sheet: a 100x100 cell table with varying values.  No headers.

Within Column A: highlight all cells >= to A1

Within Column B: highlight all cells >= to B2

Within Column C: highlight all cells >= to C3

.

.

.

Within Column CV: highlight all cells >= CV100

I could obviously do conditional formatting 100 times, but that would take forever + cannot be copied into other 100x100 arrays using the same format, but different data, unless I record it, but that would be a pretty massive chunk of code and is likely unnecessary.

This is what I found through web searching + recording a single instance of my formatting.

Sub Macro1()

'

' Macro1 Macro

' Conditional Formatting Loop

'

' Keyboard Shortcut: Ctrl+k

'

For Each Column In ActiveWorksheet.Columns

    Column.Select

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _

        , Formula1:="=$A$1"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Interior

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent2

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Next Column

End Sub

I don't know how to modify it enough to make the formula change from A1 to a variable one for A1 to CV 100 by incrementing the column and row by 1.

Thanks for any help :)

Answer 1

Try this macro:

Sub Highlight()

    Dim c As Long

    For c = 1 To 100

        With Range(Cells(1, c), Cells(100, c)).FormatConditions

            .Delete

            With .Add(Type:=xlCellValue, Operator:=xlGreaterEqual, _

                    Formula1:="=" & Cells(c, c).Address).Interior

                .PatternColorIndex = xlAutomatic

                .ThemeColor = xlThemeColorAccent2

                .TintAndShade = 0

            End With

        End With

    Next c

End Sub