Conditional Formating Excel Table

flipland

New Member
Joined
Mar 26, 2019
Messages
9
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 NameBangladeshBhutanChinaHong Kong SAR, ChinaIndonesiaJapanLao PDRSri LankaMacao SAR, ChinaMyanmarMalaysiaNepalPhilippinesSingaporeThailandVietnamKorea, Rep.MINMAX
196089904294791432355125442810115851479
19619776437564143226522674491079452564
1962100714886341392305515747211410655634

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

New Member
Joined
Mar 26, 2019
Messages
9
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?

Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Did you try my suggestion in post#2?
 

flipland

New Member
Joined
Mar 26, 2019
Messages
9

ADVERTISEMENT

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...

Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're MIN_MAX code is always looking at
Code:
[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

New Member
Joined
Mar 26, 2019
Messages
9

ADVERTISEMENT

Ah! Is there a workaround for it?

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

:)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 26, 2019
Messages
9
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,363
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top