locked
Loop through rows and find the Min/Max values of two different subsets in the same row RRS feed

  • Question

  • I am looking to write a code that will do the following:

    1. Look at each row starting with row 7 in my worksheet and find the minimum value of Columns D-G .
    2. Look in columns I-K of the same row and find the maximum value.
    3. If the Max value of I-K is greater than the Min value of D-G, I want the text in column C of the same row to be bold
    4. Loop through each row of the table (length will be variable) and perform the above steps.

    One other note is that the subsection of I-L has the potential to be larger, possibly up to Column Z and beyond.

    I figure I will need to nest loops and have some counters but I am struggling to get things started.  Any help would be greatly appreciated.
    Tuesday, August 7, 2018 7:28 PM

Answers

  • You could do this without code, using conditional formatting:

    Select C7:C1000 (or as far down as you want to go). I'll assume that C7 is the active cell within the selection.

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =MAX($K7:$XFD7)>MIN($D7:$G7)

    Click Format...

    Activate the Font tab of the Format Cells dialog.

    Select Bold.

    Click OK, then click OK again.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Wednesday, August 8, 2018 3:13 AM
    • Unproposed as answer by TimW333 Monday, August 13, 2018 2:29 PM
    • Marked as answer by TimW333 Monday, August 13, 2018 2:30 PM
    Tuesday, August 7, 2018 7:54 PM