Highlighting cells based on criteria

[rahildhody]

Hi,
I'm trying to build an engine that does the following:

There are 3 sections, all start in column J & end in column CC.

Section 1 starts in row 11, column J and ends in row 83, column J. (73 rows long & 72 columns wide)

Section 2 starts in row 89, column J & ends in row 161 column J (73 rows long & 72 columns wide)

section 3 starts in row 167 column J & ends in row 239 column J. (73 rows long & 72 columns wide)



Array of Section 1 threshold in cells F251 to F280,

Array of Section 2 threshold in cells G251 to G280,

Array of Section 3 threshold in cells H251 to H280


Row threshold in cell G249

Row 245 contains a sum function that I’ve created that sums all non-highlighted cells in the column.


What I need the code to do is this:

Look through row 249 column J. is value > row threshold cell value in G249? No? go to next column. Keep looping through columns until row249 value > value in cell G249.

Lets say that column is W.

Now Sum non-highlighted cells in column W in section 1 until it reaches or exceeds value in cell F328. Eg. Sum(W11:W22) > value in cell F328. Highlight W11:BF22 in first colour from the array. (36 columns)

Sum non-highlighted cells in column W in section 2 until it reaches or exceeds value in cell G328. Eg. Sum (W89:W102)>value in cell G328. Highlight W89:BF102 in first colour (36 columns)

Sum non-highlighted cells in column W in section 3 until it reaches or exceeds value in cell H328. Eg. Sum (W167:W176)>value in cell H328. Highlight W167:BF177 in first colour (36 columns)

This section is now complete

Recalculate row 245

Now look through row 245, column W to column CC to find the next cell that’s > cell value in G249. keep looping until row 245 > cell value in G249. Lets say sum of non-highlighted cells in column AC > threshold in G326. Condition met.

Now Sum non-highlighted cells in column AC in section 1 until it reaches or exceeds value in cell F329. Eg. Sum(AC23:AC30) > value in cell F329. Highlight AC23:BL30 in second colour. (36 columns)

Sum non-highlighted cells in column AC in section 2 until it reaches or exceeds value in cell G329. Eg. Sum (AC103:AC106)>value in cell G329. Highlight AC103:BL106 in second colour (36 columns)

Sum non-highlighted cells in column AC in section 3 until it reaches or exceeds value in cell H329. Eg. Sum (AC177:AC182)>value in cell H329. Highlight AC177:BL182 in second colour. (36 columns)

This section is now complete

Recalculate row 245

And so on

The tricky part is when it reaches column BG, the cells from the first highlight until BF are now non-highlighted cells. So when the macro is calculating the sum of the column to reach the threshold, it needs to add those values to the running sum & highlight them accordingly. this is what section 1 should look like.

i've tried creating a code to achieve this but it doesnt do exactly as intended. its highlighting cells all the way from the top of the section as opposed to only highlighting the non-highlighted cells. and it doesnt take into account the non-highlighted cells in BG when calculating the running sum.

hoping my explanation has made sense. If not, please let me know & I'll do my best to explain again. Any help on this matter will be greatly appreciated.

Cheers,
Rahil

Sub HighlightSections()
    ' Define constants
    Const START_COL As Integer = 10 ' Column J
    Const END_COL As Integer = 81 ' Column CC
    Const SECTION_WIDTH As Integer = 36
    Const THRESHOLD_ROW_BASE As Integer = 251 ' Starting row for thresholds

    ' Initialize worksheet
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Define color array
    Dim colorArray(1 To 30) As Long
    colorArray(1) = RGB(204, 227, 247)
    colorArray(2) = RGB(152, 200, 239)
    colorArray(3) = RGB(101, 172, 231)
    colorArray(4) = RGB(31, 125, 203)
    colorArray(5) = RGB(228, 200, 240)
    colorArray(6) = RGB(202, 147, 225)
    colorArray(7) = RGB(175, 94, 210)
    colorArray(8) = RGB(131, 45, 165)
    colorArray(9) = RGB(236, 248, 205)
    colorArray(10) = RGB(217, 239, 156)
    colorArray(11) = RGB(198, 232, 106)
    colorArray(12) = RGB(111, 143, 22)
    colorArray(13) = RGB(215, 238, 249)
    colorArray(14) = RGB(173, 221, 243)
    colorArray(15) = RGB(133, 203, 237)
    colorArray(16) = RGB(27, 132, 182)
    colorArray(17) = RGB(242, 242, 242)
    colorArray(18) = RGB(217, 217, 217)
    colorArray(19) = RGB(191, 191, 191)
    colorArray(20) = RGB(166, 166, 166)
    colorArray(21) = RGB(248, 208, 203)
    colorArray(22) = RGB(241, 164, 152)
    colorArray(23) = RGB(235, 118, 101)
    colorArray(24) = RGB(204, 47, 26)
    colorArray(25) = RGB(250, 240, 209)
    colorArray(26) = RGB(244, 226, 164)
    colorArray(27) = RGB(240, 213, 119)
    colorArray(28) = RGB(174, 139, 19)
    colorArray(29) = RGB(169, 236, 239)
    colorArray(30) = RGB(125, 226, 232)


    ' Load threshold value in cell G249
    Dim thresholdG249 As Double
    thresholdG249 = ws.Range("G249").Value
    Debug.Print "Threshold G249: " & thresholdG249

    Dim colorIndex As Integer
    colorIndex = 1

    Dim startCol As Integer
    startCol = START_COL

    Do
        Dim foundColumn As Boolean
        foundColumn = False

        ' Loop through columns in row 245 to find the first value > thresholdG249
        Dim col As Integer
        For col = startCol To END_COL
            If ws.Cells(245, col).Value > thresholdG249 Then
                foundColumn = True

                ' Get threshold values for the current color index
                Dim thresholdRow As Integer
                thresholdRow = THRESHOLD_ROW_BASE + colorIndex - 1
                Dim thresholdF As Double, thresholdG As Double, thresholdH As Double
                thresholdF = ws.Cells(thresholdRow, 6).Value
                thresholdG = ws.Cells(thresholdRow, 7).Value
                thresholdH = ws.Cells(thresholdRow, 8).Value

                ' Highlight Sections 1, 2, and 3
                HighlightSection ws, col, colorArray(colorIndex), thresholdF, thresholdG, thresholdH, SECTION_WIDTH

                ' Recalculate row 245
                ws.Calculate

                ' Prepare for the next round
                colorIndex = colorIndex + 1
                If colorIndex > UBound(colorArray) Then Exit Do

                ' Set the starting column for the next round to the current column
                startCol = col

                Exit For
            End If
        Next col

        If Not foundColumn Then Exit Do

    Loop
End Sub

Sub HighlightSection(ws As Worksheet, startCol As Integer, highlightColor As Long, thresholdF As Double, thresholdG As Double, thresholdH As Double, sectionWidth As Integer)
    ' Define section ranges
    Dim SEC1_START_ROW As Integer, SEC1_END_ROW As Integer
    Dim SEC2_START_ROW As Integer, SEC2_END_ROW As Integer
    Dim SEC3_START_ROW As Integer, SEC3_END_ROW As Integer

    SEC1_START_ROW = 11
    SEC1_END_ROW = 83
    SEC2_START_ROW = 89
    SEC2_END_ROW = 161
    SEC3_START_ROW = 167
    SEC3_END_ROW = 239

    ' Highlight Sections based on thresholds
    ' Section 1
    HighlightCells ws, SEC1_START_ROW, SEC1_END_ROW, startCol, highlightColor, thresholdF

    ' Section 2
    HighlightCells ws, SEC2_START_ROW, SEC2_END_ROW, startCol, highlightColor, thresholdG

    ' Section 3
    HighlightCells ws, SEC3_START_ROW, SEC3_END_ROW, startCol, highlightColor, thresholdH
End Sub

Sub HighlightCells(ws As Worksheet, startRow As Integer, endRow As Integer, col As Integer, highlightColor As Long, threshold As Double)
    Dim sumNonHighlighted As Double
    sumNonHighlighted = 0

    Dim i As Integer, j As Integer

    For i = 1 To endRow - startRow + 1
        If ws.Cells(startRow + i - 1, col).Interior.colorIndex = xlNone Then
            sumNonHighlighted = sumNonHighlighted + ws.Cells(startRow + i - 1, col).Value
            If sumNonHighlighted >= threshold Then
                For j = 1 To i
                    ws.Cells(startRow + j - 1, col).Resize(1, 36).Interior.color = highlightColor
                Next j
                Exit Sub
            End If
        End If
    Next i
End Sub