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>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,791
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
74,791
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
74,791
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
74,791
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,111
Messages
5,857,448
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top