Excel (conditional formatting)

Question

Good day,

I have a dataset and looks as follows (example). I need to highlight the highest figure between A1 and A2, then the highest between A3 and A4, the the highest between A5 and A6, and so forth. The same for column B and C etc. The highest i want highlighted in green, the lowest in red? Any ideas. It is a big dataset and manually will take too long.

Have you tried anything with conditional formatting? You will probably need a formula or two to check if A1 is greater than A2 and A2 is less than A1 — Mark Fitzgerald
I agree this will work, however, how do I add the formula to each two blocks? Because if i drag the formula down, it will then check Between A2 and A3, whereas i want it to check A1 and A2, then A3 and A4, so basically it should skip a line. Is there a macro that can help me with this? — Celeste

Chris DL Chris DL · Accepted Answer · 2017-02-22T09:48:14

The answer to the below linked question is kind of the beginning to answer your question. You will need to do this with VBA, doing something like this:

Do While i <= cols
    Do While j <= rows
         //set conditional formatting for range ij:i(j+1)
         j = j + 2
    Loop
Loop

I'll see if I can create a specific script for you.

Format Top 3 and Bottom 3 Values for each row

EDIT: I've tested this just a bit on a small data-set I created. It appears to work as requested. All you need to adjust is "cols" and "rows" to accurately represent your data-set.

EDIT2: Code has been slightly modified to fix a slight issue I found.

Sub Conditions()
Dim i As Integer, j As Integer, cols As Integer, rows As Integer
cols = 2
rows = 10
i = 1
j = 1

Do While i <= cols
    Do While j <= rows
         With Range(Cells(j, i), Cells(j + 1, i)).FormatConditions.Add(xlTop10)
                .SetFirstPriority
                .TopBottom = xlTop10Top
                .Rank = 1
                .Percent = False
                With .Interior
                   .PatternColorIndex = xlAutomatic
                   .Color = RGB(0, 255, 0)
                   .TintAndShade = 0
                End With
            End With
         j = j + 2
    Loop
    i = i + 1
    j = 1
Loop
i = 1
j = 1


Do While i <= cols
    Do While j <= rows
         With Range(Cells(j, i), Cells(j + 1, i)).FormatConditions.Add(xlTop10)
                .SetFirstPriority
                .TopBottom = xlTop10Bottom
                .Rank = 1
                .Percent = False
                With .Interior
                   .PatternColorIndex = xlAutomatic
                   .Color = RGB(255, 0, 0)
                   .TintAndShade = 0
                End With
            End With
         j = j + 2
    Loop
    i = i + 1
    j = 1
Loop

End Sub

Excel (conditional formatting)

1 Answers