Needing HELP with VBA Loop for multiple segments of rows

[abcclifton]

I have a subset of data that I am writing a MACRO to pull out into a different spreadsheet. Within this subset of data I have comparative numerical values in 9 of the 15 columns (E2:M2).

I need to be able to highlight or isolate the greatest value in each row, but while also putting this on a loop for the 2000 rows that I need that done on.


Can someone help me with the VBA code to make this happen? I am having a hard time getting it right.

 

[Rick Rothstein]

Select cells E2 (first) to M2, then extend that selection down to the end of your data (Shift-End-DownArrow). With those cells selected, click the Conditional Formatting button (Home tab, Styles panel, Conditional Formatting button, New Rule), select "Use a formula to determine which cells to format" and paste this formula into the empty field...

=E2=MAX($E2:$M2)

then click Format button, select the Fill tab and pick a fill color, then OK your way back to the work sheet... all the maximum values should not be highlighted.

 

[abcclifton]

I need it to be on a loop in the macro though. I cannot determine how to get the loop coded in while also offsetting the row by (0,-1).

I tried recording my step to set up the conditional formatting for the top 10%.... because I need the highest value to be highlighted...

This is what the recording captured. But can you help me loop this until my row x is empty. The data is always going to be a different number of rows so I need it to look at column a and assume the same loop until column a is empty.

Range("E2:M2").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = True
End With
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub HighlightMaxValueOnEachRowColumnsEthruM()
  Dim Rng As Range
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = 16383844
  For Each Rng In Range("E2", Cells(Rows.Count, "M").End(xlUp)).Rows
    Rng.Replace Application.Max(Rng), "", xlWhole, , , , False, True
  Next
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[abcclifton]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub HighlightMaxValueOnEachRowColumnsEthruM()
  Dim Rng As Range
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = 16383844
  For Each Rng In Range("E2", Cells(Rows.Count, "M").End(xlUp)).Rows
    Rng.Replace Application.Max(Rng), "", xlWhole, , , , False, True
  Next
  Application.ReplaceFormat.Clear
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

I attempted to use this macro but it doesn't change or highlight the color of the cell. They all are still white when running it.

 

[Rick Rothstein]

I attempted to use this macro but it doesn't change or highlight the color of the cell. They all are still white when running it.

It worked on the sample data I created to test it on. Obviously, there is something different about your data. Can you post a copy of your workbook to DropBox so that we can download it and test our code against it directly?