Excel VBA: Using Cell Colors For Calculation

Question

I literally just began learning how to use VBA and I've gotten stuck. What I have is an Excel sheet full of rows of inventory that has red cells on all the columns of that row where there is a discrepancy that I want to print.

In other words, if the cell color is red I ultimately want it to take the first cell of that column (the header) and append the value like as with string concatenation. And I want it to do that for all red cells in that row. For example, if the red cells in a row were in the columns for Reset and Time, I want the last column in that row to display "Reset: 20, Time: 30".

So what I have started is just trying to go cell by cell and row by row so that I can count the amount of red cells and try to put them at the end of each row. Messing with it, I've noticed that my code below will automatically just put x in adjacent cells as it increases instead of keeping it one cell like I expected. I also currently have cell(row, 60) because the column is always the same, but it doesn't like that I'm using the variable row.

    Sub CellColorRed()
'
' CellColorRed Macro
' Take headers if color is red
'
' Keyboard Shortcut: Ctrl+n
'
    Dim rng As Range, cell As Range, row As Range, x As Integer
    Set rng = Range("F2:BG9")
    For Each row In rng.Rows
        For Each cell In rng.Cells
        x = 0
            If cell.Interior.ColorIndex = 3 Then
                x = x + 1
            End If
            cell(row, 60) = x
        Next cell
    Next row
End Sub

Tim Williams Tim Williams · Accepted Answer · 2014-07-10T23:53:28

Sub GetRedCells()

    Dim rng As Range, cell As Range, row As Range, x As Integer
    Dim msg as String, sep as string

    Set rng = Range("F2:BG9")
    For Each row In rng.Rows
        msg=""
        sep=""

        For Each cell In row.Cells
            If cell.Interior.ColorIndex = 3 Then
                msg = msg & sep & Cells(1, cell.Column).Value & _
                      ": " & cell.Value 
                sep = ", "
            End If
        Next cell

        row.cells(60) = msg

    Next row

End Sub

Excel VBA: Using Cell Colors For Calculation

1 Answers