Excel VBA loop columns and rows color cell based on value-CodePudding

I would like to loop through the rows and columns (range B3:I16) of an excel worksheet. If the cell value matches my column p, I would like to color the background of the cell the color of the corresponding hex code (column O) or rgb codes (columns L:M).

I am seeing a compile error at the line "Next j" that says "Next without for" which I assume means there's an error in the previous line. I could not resolve that error.

Once I get the code to work, is there a more efficient way to check all the values in column P without a huge if else statement?

Sub format_quilt()

Dim i, j As Long

'psuedo code python style
'for i in range column number max
'       for j in range row number max
'                if (cell value == to index name in p4:p14) or (cell directly above == index name in p4:p14)
'                        color current cell using hex number


For i = 3 To Range("R2").Value
    For j = 2 To Range("R1").Value
        If (Cells(i, j).Value = Range("P4").Value) Or (Cells(i - 1, j).Value = Range("P4").Value) Then
        Cells(i, j).Interior.Color = RGB(Range("L4").Value, Range("M4").Value, Range("n4").Value)
    
    
    Next j

Next i


End Sub

CodePudding user response：

You can use Match() to check your list in Col P.

For example (copying the fill color from the matched cell):

Option Explicit

Sub format_quilt()

    Dim c As Range, ws As Worksheet, m, rngList As Range
    Dim i As Long, j As Long

    Set ws = ActiveSheet 'or some specific sheet
    Set rngList = ws.Range("P4:P14")  'lookup range

    For i = 3 To ws.Range("R2").Value
        For j = 2 To ws.Range("R1").Value
            Set c = ws.Cells(i, j)
            m = Application.Match(c.Value, rngList, 0)
            If Not IsError(m) Then  'got a match?
                c.Interior.Color = rngList.Cells(m).Interior.Color
            Else
                c.Interior.ColorIndex = xlNone 'clear if no match
            End If
        Next j
    Next i
End Sub