Conditional Formating Excel Table

[flipland]

Hi. I would like to have conditional formatting done on an Excel Table based on the MIN and MAX values of every row in the table. Cells of the MIN values are to be highligted in RED with WHITE font. Celss of the MAX values are to be highlighted in GREEN with WHITE font.

I was able to find VBA code to conditionally format by row, but am stuck as to how to make it loop down to the next row. The code is copy-pasted below, as well as a snippet of the table. Would appreciate any help on this. Thanks!

CODE:
Public Sub MIN_MAX_Format()

ActiveSheet.Range("A1").Select
ActiveCell.Offset(1, 1).Select
Range("b2:r2").Select


Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = ActiveSheet.Range("B2:R2")

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$U2")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$T2")

'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With

With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

ActiveCell.Offset(1, 0).Select


End Sub



The table looks like this:

https://drive.google.com/file/d/1yTWNE9WaHTzBJRUfXZ_VKq5v8ZdJZdFf/view?usp=sharing

Country Name	Bangladesh	Bhutan	China	Hong Kong SAR, China	Indonesia	Japan	Lao PDR	Sri Lanka	Macao SAR, China	Myanmar	Malaysia	Nepal	Philippines	Singapore	Thailand	Vietnam	Korea, Rep.	MIN	MAX
1960	89		90	429		479		143			235	51	254	428	101		158		51	479
1961	97		76	437		564		143			226	52	267	449	107		94		52	564
1962	100		71	488		634		139			230	55	157	472	114		106		55	634

<tbody>
</tbody>

 

[Fluff]

How about

Sub flipland()
   With Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 18))
      .FormatConditions.Delete
      .FormatConditions.Add xlExpression, , "=A2=MAX($B2:$R2)"
      .FormatConditions.Add xlExpression, , "=A2=MIN($B2:$R2)"
      With .FormatConditions(1)
         .Interior.Color = vbGreen
         .Font.Color = vbWhite
      End With
      With .FormatConditions(2)
         .Interior.Color = vbRed
         .Font.Color = vbWhite
      End With
   End With
End Sub

 

[flipland]

Hi...

I think I got the loop part going using a FOR EACH LOOP. The first row of data now is being conditionally formatted. It then goes to the first cell of the second row, but it keeps formatting the first row of data. What am I missing here?

Public Sub ForColorLoop()


    For Each rg In Selection
        MIN_MAX_Format

        'go to next row/range
        ActiveCell.Offset(1, 0).Select

    'go to next row or range
    Next rg

End Sub

Public Sub MIN_MAX_Format()

    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
    Set rg = ActiveSheet.Range("B2:R2")

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$U2")
    Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$T2")

    'define the format applied for each conditional format
    With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
    End With

    With cond2
    .Interior.Color = vbRed
    .Font.Color = vbWhite
    End With


End Sub

 

[Fluff]

Did you try my suggestion in post#2?

 

[flipland]

THANK YOU! That is so much more elegant than what I was trying to with the second iteration I posted a few mins ago with the LOOP code. I'd still like to understand what I was missing with that one and would appreciate feedback on it. Re-posting it below...

Public Sub ForColorLoop()


    For Each rg In Selection
        MIN_MAX_Format

        'go to next row/range
        ActiveCell.Offset(1, 0).Select

    'go to next row or range
    Next rg

End Sub

___________________

Public Sub MIN_MAX_Format()

    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
    Set rg = ActiveSheet.Range("B2:R2")

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$U2")
    Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$T2")

    'define the format applied for each conditional format
    With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
    End With

    With cond2
    .Interior.Color = vbRed
    .Font.Color = vbWhite
    End With


End Sub

 

[Fluff]

You're MIN_MAX code is always looking at

[I]ActiveSheet.Range("B2:R2")[/I]

So it will only format that row.
Also even if that line was altered to look at the active cell row3 & below would not work properly, because they would still be looking at the value in U2 & T2

 

[flipland]

Ah! Is there a workaround for it?

AND, THANK YOU for your suggestion in post #2 !!!!!

 

[flipland]

Ah! Is there a workaround for it?

AND, THANK YOU for your suggestion in post #2 !!!!!

I mean..... is there a way to fix it with the version I posted? Or none at all?

 

[Fluff]

Yes it can be made to work, but I don't see the point when the code I posted does the same thing, but without loops (which are slow).
Also with your code, rather than having 2 conditional format rules, you will have 2 for each & every row with data, which can slow things down.

 

[flipland]

Yes it can be made to work, but I don't see the point when the code I posted does the same thing, but without loops (which are slow).
Also with your code, rather than having 2 conditional format rules, you will have 2 for each & every row with data, which can slow things down.

Most certainly your code is so much more efficient. Again, thank you very much!
I just wanted to know if, on the other one, I was at least on the right track and where it is that I am making an error.
Much appreciated!