VBA Code or Conditional Formatting Formula for highlighting cells within a Sub-Group

K

KWL

New Member
Joined
Apr 17, 2011
Messages
16
Looking for some help…. I am using Excel 2007 with a WIN XP OS…. My objective is to color code each cell in Column F within each sub-group based on the following instructions:<o:p></o:p>
1) Column F contains a percentage…. Each sub-group in Column F totals 100%.... Each sub-group is separated as shown on the enclosed sample.<o:p></o:p>
2) Starting with the highest percentage, followed by the next highest, (etc.), I want to sum each subsequent cell until reaching a target threshold (total sum) of 62%.... All cells in this grouping to be highlighted “yellow”.<o:p></o:p>
3) Once the 62% target threshold is reached, the very next highest subsequent cell percentage is highlighted “light olive”…. If there is a tie, the lower number shown in Column E serves as the tie-breaker.<o:p></o:p>
4) All remaining cells within the sub-group are highlighted “light brown” including those denoted by 0%.<o:p></o:p>
5) Finally, if the cell is blank and absent of a % call out, then no highlight is required.<o:p></o:p>
<o:p></o:p>
I process a daily spreadsheet having typically in excess of 1000 rows and in turn, I’m looking for a method to automate the color coding…. Thanks in advance for all suggestions and help provided.<o:p></o:p>
<o:p></o:p>
Best regards,<o:p></o:p>
<o:p></o:p>
KWL

[TABLE="width: 297"]
<TBODY>[TR]
[TD="class: xl70, width: 41, bgcolor: #e6b9b8"]PGM
[/TD]
[TD="class: xl71, width: 142, bgcolor: #e6b9b8"]HORSE
[/TD]
[TD="class: xl68, width: 37, bgcolor: #7f7f7f"]MLO
[/TD]
[TD="class: xl69, width: 48, bgcolor: #fac090"]PRICE LINE
[/TD]
[TD="class: xl72, width: 64, bgcolor: #fac090"]PL RANK
[/TD]
[TD="class: xl73, width: 64, bgcolor: #8db4e3"]New PACE
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]Text
[/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Morse Code Mama
[/TD]
[TD="class: xl74, bgcolor: transparent"]3.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]1.8
[/TD]
[TD="class: xl79, bgcolor: transparent"]1
[/TD]
[TD="class: xl83, bgcolor: yellow"]29%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Venus de Milo
[/TD]
[TD="class: xl74, bgcolor: transparent"]6.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]6.3
[/TD]
[TD="class: xl79, bgcolor: transparent"]5
[/TD]
[TD="class: xl84, bgcolor: #fac090"]10%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Take What You Can
[/TD]
[TD="class: xl74, bgcolor: transparent"]2.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]5.5
[/TD]
[TD="class: xl79, bgcolor: transparent"]3
[/TD]
[TD="class: xl84, bgcolor: #fac090"]9%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Spittin' Feathers
[/TD]
[TD="class: xl74, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl82, bgcolor: transparent"]3.9
[/TD]
[TD="class: xl79, bgcolor: transparent"]2
[/TD]
[TD="class: xl83, bgcolor: yellow"]21%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Summon the Rain
[/TD]
[TD="class: xl74, bgcolor: transparent"]3.5
[/TD]
[TD="class: xl82, bgcolor: transparent"]6.2
[/TD]
[TD="class: xl79, bgcolor: transparent"]4
[/TD]
[TD="class: xl85, bgcolor: #c2d69a"]16%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]Pegasus Saphire
[/TD]
[TD="class: xl74, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]67.0
[/TD]
[TD="class: xl79, bgcolor: transparent"]6
[/TD]
[TD="class: xl83, bgcolor: yellow"]17%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]Text
[/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Chasing Gold
[/TD]
[TD="class: xl74, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl82, bgcolor: transparent"]3.8
[/TD]
[TD="class: xl79, bgcolor: transparent"]2
[/TD]
[TD="class: xl85, bgcolor: #c2d69a"]21%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]King Rock
[/TD]
[TD="class: xl74, bgcolor: transparent"]1.4
[/TD]
[TD="class: xl82, bgcolor: transparent"]1.1
[/TD]
[TD="class: xl79, bgcolor: transparent"]1
[/TD]
[TD="class: xl84, bgcolor: #fac090"]1%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Tiger Distinction
[/TD]
[TD="class: xl74, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]10.6
[/TD]
[TD="class: xl79, bgcolor: transparent"]4
[/TD]
[TD="class: xl84, bgcolor: #fac090"]2%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]8
[/TD]
[TD="class: xl77, bgcolor: transparent"]Richie Rules
[/TD]
[TD="class: xl74, bgcolor: transparent"]30.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]137.5
[/TD]
[TD="class: xl79, bgcolor: transparent"]8
[/TD]
[TD="class: xl84, bgcolor: #fac090"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Bahim
[/TD]
[TD="class: xl74, bgcolor: transparent"]4.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl79, bgcolor: transparent"]3
[/TD]
[TD="class: xl83, bgcolor: yellow"]36%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Patriotic Dream
[/TD]
[TD="class: xl74, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]13.0
[/TD]
[TD="class: xl79, bgcolor: transparent"]5
[/TD]
[TD="class: xl84, bgcolor: #fac090"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Silver Tuxedo
[/TD]
[TD="class: xl74, bgcolor: transparent"]15.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]57.1
[/TD]
[TD="class: xl79, bgcolor: transparent"]6
[/TD]
[TD="class: xl84, bgcolor: #fac090"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]7
[/TD]
[TD="class: xl77, bgcolor: transparent"]Winged Hero
[/TD]
[TD="class: xl74, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]62.4
[/TD]
[TD="class: xl79, bgcolor: transparent"]7
[/TD]
[TD="class: xl83, bgcolor: yellow"]40%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]Text
[/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]8
[/TD]
[TD="class: xl77, bgcolor: transparent"]Broadway Peyton
[/TD]
[TD="class: xl74, bgcolor: transparent"]12.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]7.9
[/TD]
[TD="class: xl79, bgcolor: transparent"]4
[/TD]
[TD="class: xl83, bgcolor: yellow"]100%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]7
[/TD]
[TD="class: xl77, bgcolor: transparent"]Cowgirls in Heaven
[/TD]
[TD="class: xl74, bgcolor: transparent"]4.5
[/TD]
[TD="class: xl82, bgcolor: transparent"]6.9
[/TD]
[TD="class: xl79, bgcolor: transparent"]3
[/TD]
[TD="class: xl84, bgcolor: #fac090"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]Estuary
[/TD]
[TD="class: xl74, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl82, bgcolor: transparent"]3.1
[/TD]
[TD="class: xl79, bgcolor: transparent"]2
[/TD]
[TD="class: xl85, bgcolor: #c2d69a"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Rebecca Rusch
[/TD]
[TD="class: xl74, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]39.6
[/TD]
[TD="class: xl79, bgcolor: transparent"]8
[/TD]
[TD="class: xl84, bgcolor: #fac090"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Abou
[/TD]
[TD="class: xl74, bgcolor: transparent"]3.5
[/TD]
[TD="class: xl82, bgcolor: transparent"]2.9
[/TD]
[TD="class: xl79, bgcolor: transparent"]1
[/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Gem's Pride
[/TD]
[TD="class: xl74, bgcolor: transparent"]5.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]8.3
[/TD]
[TD="class: xl79, bgcolor: transparent"]5
[/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Ascending Angel
[/TD]
[TD="class: xl74, bgcolor: transparent"]6.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]13.8
[/TD]
[TD="class: xl79, bgcolor: transparent"]6
[/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Hypno
[/TD]
[TD="class: xl74, bgcolor: transparent"]6.0
[/TD]
[TD="class: xl82, bgcolor: transparent"]16.7
[/TD]
[TD="class: xl79, bgcolor: transparent"]7
[/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]



 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
hi, Please test on a copy of your workbook. regards

Code: 
Sub test()
  'http://www.mrexcel.com/forum/excel-questions/753150-visual-basic-applications-code-conditional-formatting-formula-highlighting-cells-within-sub-group.html
  'assumes data per example
  Const dbl_THRESHHOLD As Double = 0.62


  Dim i As Long
  Dim MySum As Double
  Dim rng As Range
  Dim rngTemp As Range


  Application.ScreenUpdating = False


  'insert temporary column G for sorting
  Columns("G").Insert
  With Range("G2:G" & ActiveSheet.UsedRange.Rows.Count)
    .Formula = "=if(len(rc[-1])=0,"""",row())"
    .Value2 = .Value2
  End With


  'loop through each data block
  For Each rng In Columns("G").SpecialCells(xlCellTypeConstants).Areas
  
    'work with columns "E:G"
    Set rngTemp = rng.Resize(, 3).Offset(, -2)
    
    With rngTemp
      'to simplify coding, temporarily sort each data block on column F
      .Sort key1:=.Cells(2), order1:=xlDescending, key2:=.Cells(1), order2:=xlDescending, Header:=xlNo
      
      'sort out the formatting
      .Columns(2).Interior.ColorIndex = 40
      MySum = 0
      For i = 1 To rngTemp.Rows.Count
        .Cells(i, 2).Interior.ColorIndex = 6
        MySum = MySum + rngTemp.Cells(i, 2)
        If MySum > dbl_THRESHHOLD Then
          .Cells(i + 1, 2).Interior.ColorIndex = 50
          Exit For
        End If
      Next i
      
      'revert to original sort order, using temporary column G
      .Sort key1:=.Cells(3), order1:=xlAscending, Header:=xlNo
    End With
    
  Next rng
  
  'delete temporary column G
  Columns("G").Delete
End Sub
 
Upvote 0
Fazza.... Thanks so much for the help and the generous gift of your time.... The code you supplied is brilliant; however, I get the following error code which seems to occur within the second sub-group.... I am providing a screen shot of both the error code and the test workbook for your review.<o:p></o:p>
<o:p></o:p>
Thanks again and best regards,<o:p></o:p>
<o:p></o:p>
KWL<o:p></o:p>
<o:p></o:p>
Error Code: MySum = MySum + rngTemp.Cells(i, 2) = Type Mismatch<TYPE Mismatch>
[TABLE="width: 337"]
<TBODY>[TR]
[TD="class: xl72, width: 40, bgcolor: #e6b8b7"]PGM
[/TD]
[TD="class: xl73, width: 138, bgcolor: #e6b8b7"]HORSE
[/TD]
[TD="class: xl70, width: 36, bgcolor: gray"]MLO
[/TD]
[TD="class: xl71, width: 47, bgcolor: #fabf8f"]PRICE LINE
[/TD]
[TD="class: xl74, width: 62, bgcolor: #fabf8f"]PL RANK
[/TD]
[TD="class: xl75, width: 62, bgcolor: #8db4e2"]New PACE
[/TD]
[TD="class: xl86, width: 62, bgcolor: #8db4e2"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"]Text
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Morse Code Mama
[/TD]
[TD="class: xl78, bgcolor: transparent"]3.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]1.8
[/TD]
[TD="class: xl80, bgcolor: transparent"]1
[/TD]
[TD="class: xl88, bgcolor: yellow"]29%
[/TD]
[TD="class: xl81, bgcolor: transparent"]500%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Venus de Milo
[/TD]
[TD="class: xl78, bgcolor: transparent"]6.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]6.3
[/TD]
[TD="class: xl80, bgcolor: transparent"]5
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]10%
[/TD]
[TD="class: xl81, bgcolor: transparent"]600%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Take What You Can
[/TD]
[TD="class: xl78, bgcolor: transparent"]2.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]5.5
[/TD]
[TD="class: xl80, bgcolor: transparent"]3
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]9%
[/TD]
[TD="class: xl81, bgcolor: transparent"]700%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Spittin' Feathers
[/TD]
[TD="class: xl78, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]3.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]2
[/TD]
[TD="class: xl88, bgcolor: yellow"]21%
[/TD]
[TD="class: xl81, bgcolor: transparent"]800%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Summon the Rain
[/TD]
[TD="class: xl78, bgcolor: transparent"]3.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]6.2
[/TD]
[TD="class: xl80, bgcolor: transparent"]4
[/TD]
[TD="class: xl89, bgcolor: #339966"]16%
[/TD]
[TD="class: xl81, bgcolor: transparent"]900%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]Pegasus Saphire
[/TD]
[TD="class: xl78, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]67.0
[/TD]
[TD="class: xl80, bgcolor: transparent"]6
[/TD]
[TD="class: xl88, bgcolor: yellow"]17%
[/TD]
[TD="class: xl81, bgcolor: transparent"]1000%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"]Text
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Chasing Gold
[/TD]
[TD="class: xl78, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]3.8
[/TD]
[TD="class: xl80, bgcolor: transparent"]1
[/TD]
[TD="class: xl88, bgcolor: yellow"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]2000%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]King Rock
[/TD]
[TD="class: xl78, bgcolor: transparent"]1.4
[/TD]
[TD="class: xl83, bgcolor: transparent"]1.1
[/TD]
[TD="class: xl80, bgcolor: transparent"]6
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]40%
[/TD]
[TD="class: xl81, bgcolor: transparent"]2200%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Tiger Distinction
[/TD]
[TD="class: xl78, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]10.6
[/TD]
[TD="class: xl80, bgcolor: transparent"]4
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]36%
[/TD]
[TD="class: xl81, bgcolor: transparent"]1900%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]8
[/TD]
[TD="class: xl77, bgcolor: transparent"]Richie Rules
[/TD]
[TD="class: xl78, bgcolor: transparent"]30.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]137.5
[/TD]
[TD="class: xl80, bgcolor: transparent"]5
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]21%
[/TD]
[TD="class: xl81, bgcolor: transparent"]1500%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Bahim
[/TD]
[TD="class: xl78, bgcolor: transparent"]4.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl80, bgcolor: transparent"]3
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]2%
[/TD]
[TD="class: xl81, bgcolor: transparent"]1700%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Patriotic Dream
[/TD]
[TD="class: xl78, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]13.0
[/TD]
[TD="class: xl80, bgcolor: transparent"]7
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]1%
[/TD]
[TD="class: xl81, bgcolor: transparent"]1600%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Silver Tuxedo
[/TD]
[TD="class: xl78, bgcolor: transparent"]15.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]57.1
[/TD]
[TD="class: xl80, bgcolor: transparent"]2
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]1800%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]7
[/TD]
[TD="class: xl77, bgcolor: transparent"]Winged Hero
[/TD]
[TD="class: xl78, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]62.4
[/TD]
[TD="class: xl80, bgcolor: transparent"]8
[/TD]
[TD="class: xl87, bgcolor: #ffcc99"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]2100%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"]Text
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]8
[/TD]
[TD="class: xl77, bgcolor: transparent"]Broadway Peyton
[/TD]
[TD="class: xl78, bgcolor: transparent"]12.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]7.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]4
[/TD]
[TD="class: xl81, bgcolor: transparent"]100%
[/TD]
[TD="class: xl81, bgcolor: transparent"]2700%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]7
[/TD]
[TD="class: xl77, bgcolor: transparent"]Cowgirls in Heaven
[/TD]
[TD="class: xl78, bgcolor: transparent"]4.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]6.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]3
[/TD]
[TD="class: xl81, bgcolor: transparent"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]2800%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]Estuary
[/TD]
[TD="class: xl78, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]3.1
[/TD]
[TD="class: xl80, bgcolor: transparent"]2
[/TD]
[TD="class: xl81, bgcolor: transparent"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]2900%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Rebecca Rusch
[/TD]
[TD="class: xl78, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]39.6
[/TD]
[TD="class: xl80, bgcolor: transparent"]8
[/TD]
[TD="class: xl81, bgcolor: transparent"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]3000%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Abou
[/TD]
[TD="class: xl78, bgcolor: transparent"]3.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]2.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]1
[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Gem's Pride
[/TD]
[TD="class: xl78, bgcolor: transparent"]5.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]8.3
[/TD]
[TD="class: xl80, bgcolor: transparent"]5
[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Ascending Angel
[/TD]
[TD="class: xl78, bgcolor: transparent"]6.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]13.8
[/TD]
[TD="class: xl80, bgcolor: transparent"]6
[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Hypno
[/TD]
[TD="class: xl78, bgcolor: transparent"]6.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]16.7
[/TD]
[TD="class: xl80, bgcolor: transparent"]7
[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent"]TEXT
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"]Text
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]2
[/TD]
[TD="class: xl77, bgcolor: transparent"]Maura's Image
[/TD]
[TD="class: xl78, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]51.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]7
[/TD]
[TD="class: xl81, bgcolor: transparent"]2%
[/TD]
[TD="class: xl81, bgcolor: transparent"]3900%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]4
[/TD]
[TD="class: xl77, bgcolor: transparent"]Holy Maria's Storm
[/TD]
[TD="class: xl78, bgcolor: transparent"]20.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]51.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]7
[/TD]
[TD="class: xl81, bgcolor: transparent"]11%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4000%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]9
[/TD]
[TD="class: xl77, bgcolor: transparent"]Tactful Cathy
[/TD]
[TD="class: xl78, bgcolor: transparent"]2.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]3.2
[/TD]
[TD="class: xl80, bgcolor: transparent"]2
[/TD]
[TD="class: xl81, bgcolor: transparent"]22%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4100%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]8
[/TD]
[TD="class: xl77, bgcolor: transparent"]Meet Georgeanna
[/TD]
[TD="class: xl78, bgcolor: transparent"]15.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]14.6
[/TD]
[TD="class: xl80, bgcolor: transparent"]6
[/TD]
[TD="class: xl81, bgcolor: transparent"]2%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4200%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]3
[/TD]
[TD="class: xl77, bgcolor: transparent"]Rare Earth
[/TD]
[TD="class: xl78, bgcolor: transparent"]12.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]2.4
[/TD]
[TD="class: xl80, bgcolor: transparent"]1
[/TD]
[TD="class: xl81, bgcolor: transparent"]5%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4300%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]Spring a Latch
[/TD]
[TD="class: xl78, bgcolor: transparent"]1.8
[/TD]
[TD="class: xl83, bgcolor: transparent"]6.9
[/TD]
[TD="class: xl80, bgcolor: transparent"]4
[/TD]
[TD="class: xl81, bgcolor: transparent"]23%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4400%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl77, bgcolor: transparent"]Mz Mill
[/TD]
[TD="class: xl78, bgcolor: transparent"]3.5
[/TD]
[TD="class: xl83, bgcolor: transparent"]5.1
[/TD]
[TD="class: xl80, bgcolor: transparent"]3
[/TD]
[TD="class: xl81, bgcolor: transparent"]5%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4500%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl77, bgcolor: transparent"]Cotton Kingdom
[/TD]
[TD="class: xl78, bgcolor: transparent"]8.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]13.0
[/TD]
[TD="class: xl80, bgcolor: transparent"]5
[/TD]
[TD="class: xl81, bgcolor: transparent"]30%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4600%
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]7
[/TD]
[TD="class: xl77, bgcolor: transparent"]Sunday Cruisin'
[/TD]
[TD="class: xl78, bgcolor: transparent"]30.0
[/TD]
[TD="class: xl83, bgcolor: transparent"]101.3
[/TD]
[TD="class: xl80, bgcolor: transparent"]9
[/TD]
[TD="class: xl81, bgcolor: transparent"]0%
[/TD]
[TD="class: xl81, bgcolor: transparent"]4700%
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Fazza.... Regarding the "error code", perhaps the following is a better illustration.... Thanks again:

'sort out the formatting<o:p></o:p>
.Columns(2).Interior.ColorIndex = 40<o:p></o:p>
MySum = 0<o:p></o:p>
For i = 1 To rngTemp.Rows.Count<o:p></o:p>
.Cells(i, 2).Interior.ColorIndex = 6<o:p></o:p>
ERROR Line----> MySum = MySum + rngTemp.Cells(i, 2) (Error = Type Mismatch)<o:p></o:p>
If MySum > dbl_THRESHHOLD Then<o:p></o:p>
.Cells(i + 1, 2).Interior.ColorIndex = 50<o:p></o:p>
Exit For<o:p></o:p>
End If<o:p></o:p>
Next i
 
Last edited:
Upvote 0
A big thank you for providing the screen shot: it helps a lot. The old picture being worth a thousand words.

Certainly I've done something on that line that I thought I never do. Whoops. That can be overcome by adding .value2 a the end of the line. It becomes,
MySum = MySum + rngTemp.Cells(i, 2).value2

However I can't replicate the error - either before or after the above fix up.

I wonder if the entries in 'New Pace' numeric or text? In the screen images they are left justified - so they look to be text. When I copy & paste to Excel for testing they paste in as numeric values. As numeric values all works OK. If they were text though, the line of code that tries to add a cell entry to a running total would error like you're seeing, I think.

what do the column F entries look like after CTRL-SHIFT-~

Text or numeric?
 
Upvote 0
Fazza,

Actually, I was coping and pasting Column F from a different column within the worksheet.... All values were rounded (zero decimal places) and intended to be numeric; however, for some unknown reason, the actual 0% values were converted and imported as text causing the "type mismatch" error.... All is now good!

Many, many thanks for your help and again, my sincerest gratitude for the gift of your time and energy!

Best regards,

KWL
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
How do I rank a table, formula or macro please?
Replies
5
Views
124
Peter_SSs
Peter_SSs
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
155
dreid1011
dreid1011
M
  • Solved
Highlight cell when a percentage amount has been met.
Replies
6
Views
276
Peter_SSs
Peter_SSs
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
406
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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
Back
Top