0

I have an Excel spreadsheet with 480 columns and 1451 rows. I need to find the highest value in each row. I understand how to use the conditional formatting tool to find the highest value within a range. However, I don't understand how to apply that same rule to each row. As you can imagine, it's a little impractical to create a new rule for each of the 1451 rows. Likewise, I can't state the range as the worksheet as I'm looking for one value per row. I can't highlight the column, as the highest value is likely to be in different columns in each row.

On another post, someone had suggested dragging the corner of the selection in much the same way as you would to "fill" an area, obviously, changing the fill settings to formatting only. However, when I try that, all it seems to do is delete my conditional formatting. What am I doing wrong?

3 Answers 3

0

For Conditional Formatting, in the "Applies to" field, you can name various ranges separated by a comma. Ex: "$A$1:$A$20,$C$1:$C$20,$E$1:$E$20". This allows that rule to apply to columns A, C & E.

2
  • This would seem cleaner than 1451 rules, but I'm not sure it would save me time as I'd have to comma separate 1451 ranges. Sep 2, 2019 at 4:57
  • And this doesn't return one result per range. It returns one result only for all included ranges. Sep 2, 2019 at 5:26
0
  1. Open VBA editor (Alt-F11)
  2. Insert - Module
  3. Insert below code into it:

    Public Function IsMax(Target As Range) As Boolean
    If Target.Value = WorksheetFunction.Max(Target.EntireRow) Then
        IsMax = True
    Else
        IsMax = False
    End If
    End Function
    
  4. Conditional formatting - Rules

  5. Create new rule:

    a. Formula: =IsMax(A1)

    b. Range applied to: =$A$1:$XY$1451 (justify XY to your data range)

    c. Format: set one which you need

2
  • Is the UDF necessary? =A1=MAX(1:1) should do the same thing.
    – user385793
    Sep 2, 2019 at 7:09
  • @Jeeped You're right. Convert your comment to the answer.
    – Akina
    Sep 2, 2019 at 7:30
0

Create a new column for a dedicated dynamic "max" value for the row. Use the MAX formula and set the range on the first row. Now copy the cell and paste it into the next row below, it should paste the formula for the new row using the new values and same range. Finally when you reference a max value somewhere else, reference the dedicated dynamic "max" value you created. There are multiple ways to reference a cell, check out A2 vs $A2 vs A$2.

5
  • Except that the value of the maximum number is less important to me than the location of the maximum number. I could set up conditional formating to test if a number is equal to the maximum, but this seems like an extra step when I can already directly conditional format for the max. Besides, it leaves me with the same problem of applying conditional formatting to different rows. Plus I'd have to copy and paste 1451 times, which seems tedious. Sep 2, 2019 at 15:57
  • You won’t need to to copy and paste individually, I promise you, you will understand & find a way to be able to do it. There are a lot of very smart people on this site. You can add an addition row to the dynamic data under MAX that contains the ROW(of the MAX value above it), add new row below & include COLUMN() of the max value two rows above, each additional row below can reference the rams MAX values related information. Then add the new dedicated data sets pulling from the MAX values and associated rows.
    – Theologin
    Sep 2, 2019 at 17:26
  • I'm not sure I'm following you. You're saying create one column for the max in each row (so reading down the column, max row1, max row2, etc). Then in a second column create some sort of formula that identifies which column heading the max falls under (in other words max row 1 is in column Q, max of row 2 is in column g, etc)? First, I don't really know what the second formula would be. Second, I suppose having a label identifying the column would be better than nothing, but I'd still prefer the max itself color tagged so I don't have to keep scrolling back and forth to reference Sep 3, 2019 at 4:25
  • Once again, the value of the maximum really isn't important. I don't care if it is 5 or 5,000. What I need to do is mark which column each row max falls under. Sep 3, 2019 at 4:27
  • It’s not for display purposes. Build a tab for display purposes and lists what you want to see however you want now. Convert it to a column and use conditional formatting.
    – Theologin
    Sep 3, 2019 at 14:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .