Contare le celle colorate in each colonna e restituire il count

Ho cercato di trovare un modo più semplice per restituire la quantità di celle colorate per colonna. Il mio codice finora:

Sub errors() Dim Sheet1 As String Dim mycell As Range Dim datecol As Long Dim col As Long Sheet1 = "different" For Each mycell In ActiveWorkbook.Worksheets(shtSheet1).Range("B:B") If mycell.Interior.Color = vbRed Then datecol = datecol + 1 End If Next mycell Debug.Print datecol End Sub 

Se ho 40 colonne questo è abbastanza noioso e lento, c'è un modo migliore per farlo (piuttosto nuovo nell'apprendimento vba), quindi ripeto di più per ciascun loop e specificando la colonna. Ho bisogno di aiuto per migliorare il codice o un nuovo metodo per restituire la quantità di celle colorate per colonna.

Il tuo modo va bene, ma può essere raffinato –

 Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim cntclr As Integer cntclr = 0 For i = 1 To lastrow If Cells(i, 1).Interior.Color = vbRed Then cntclr = cntclr + 1 End If Next MsgBox (cntclr) End Sub Dim ultimo come integer Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim cntclr As Integer cntclr = 0 For i = 1 To lastrow If Cells(i, 1).Interior.Color = vbRed Then cntclr = cntclr + 1 End If Next MsgBox (cntclr) End Sub lastrow = Celle (row.count, "A"). Fine (xlUp) .Row Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim cntclr As Integer cntclr = 0 For i = 1 To lastrow If Cells(i, 1).Interior.Color = vbRed Then cntclr = cntclr + 1 End If Next MsgBox (cntclr) End Sub Per i = 1 Per finire Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim cntclr As Integer cntclr = 0 For i = 1 To lastrow If Cells(i, 1).Interior.Color = vbRed Then cntclr = cntclr + 1 End If Next MsgBox (cntclr) End Sub 

Se vuoi farlo per each colonna, questo lo farà e printing i risultati, basta cambiare l'intervallo in cui l'arrays si popola

 Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim lastcol As Integer lastcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim arr() As String ReDim arr(1 To lastcol, 1) Dim cntClr As Integer Dim strCol As String Dim strCnt As String For j = 1 To lastcol cntClr = 0 For i = 1 To lastrow If Cells(i, j).Interior.Color = vbRed Then cntClr = cntClr + 1 End If Next strCol = "Col " & Chr(64 + j) strCnt = Str(cntClr) arr(j, 0) = strCol arr(j, 1) = cntClr Next Range("D1:E" & lastcol) = arr() End Sub Dim ultimo come integer Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim lastcol As Integer lastcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim arr() As String ReDim arr(1 To lastcol, 1) Dim cntClr As Integer Dim strCol As String Dim strCnt As String For j = 1 To lastcol cntClr = 0 For i = 1 To lastrow If Cells(i, j).Interior.Color = vbRed Then cntClr = cntClr + 1 End If Next strCol = "Col " & Chr(64 + j) strCnt = Str(cntClr) arr(j, 0) = strCol arr(j, 1) = cntClr Next Range("D1:E" & lastcol) = arr() End Sub lastrow = Celle (row.count, "A"). Fine (xlUp) .Row Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim lastcol As Integer lastcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim arr() As String ReDim arr(1 To lastcol, 1) Dim cntClr As Integer Dim strCol As String Dim strCnt As String For j = 1 To lastcol cntClr = 0 For i = 1 To lastrow If Cells(i, j).Interior.Color = vbRed Then cntClr = cntClr + 1 End If Next strCol = "Col " & Chr(64 + j) strCnt = Str(cntClr) arr(j, 0) = strCol arr(j, 1) = cntClr Next Range("D1:E" & lastcol) = arr() End Sub Dim arr () come string Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim lastcol As Integer lastcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim arr() As String ReDim arr(1 To lastcol, 1) Dim cntClr As Integer Dim strCol As String Dim strCnt As String For j = 1 To lastcol cntClr = 0 For i = 1 To lastrow If Cells(i, j).Interior.Color = vbRed Then cntClr = cntClr + 1 End If Next strCol = "Col " & Chr(64 + j) strCnt = Str(cntClr) arr(j, 0) = strCol arr(j, 1) = cntClr Next Range("D1:E" & lastcol) = arr() End Sub Per j = 1 To lastcol Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim lastcol As Integer lastcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim arr() As String ReDim arr(1 To lastcol, 1) Dim cntClr As Integer Dim strCol As String Dim strCnt As String For j = 1 To lastcol cntClr = 0 For i = 1 To lastrow If Cells(i, j).Interior.Color = vbRed Then cntClr = cntClr + 1 End If Next strCol = "Col " & Chr(64 + j) strCnt = Str(cntClr) arr(j, 0) = strCol arr(j, 1) = cntClr Next Range("D1:E" & lastcol) = arr() End Sub Per i = 1 Per finire Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Dim lastcol As Integer lastcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim arr() As String ReDim arr(1 To lastcol, 1) Dim cntClr As Integer Dim strCol As String Dim strCnt As String For j = 1 To lastcol cntClr = 0 For i = 1 To lastrow If Cells(i, j).Interior.Color = vbRed Then cntClr = cntClr + 1 End If Next strCol = "Col " & Chr(64 + j) strCnt = Str(cntClr) arr(j, 0) = strCol arr(j, 1) = cntClr Next Range("D1:E" & lastcol) = arr() End Sub 

Uso

 Sub errors() Dim Sheet1 As String Dim mycell As Range Dim datecol As Long Dim col As Long Sheet1 = "different" Dim ws As Worksheet, lastrow As Long Dim myrng as Range Set ws = ActiveWorkbook.Sheets(Sheet1) lastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row ' If you think you may have colored cells with no data ' Set myrng = ws.UsedRange.Rows ' lastrow = myrng(myrng.Count).Row Set myrng = ws.Range("B1:B" & lastrow) For Each mycell In myrng If mycell.Interior.Color = vbRed Then datecol = datecol + 1 End If Next mycell Debug.Print datecol End Sub Dim col As Long Sub errors() Dim Sheet1 As String Dim mycell As Range Dim datecol As Long Dim col As Long Sheet1 = "different" Dim ws As Worksheet, lastrow As Long Dim myrng as Range Set ws = ActiveWorkbook.Sheets(Sheet1) lastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row ' If you think you may have colored cells with no data ' Set myrng = ws.UsedRange.Rows ' lastrow = myrng(myrng.Count).Row Set myrng = ws.Range("B1:B" & lastrow) For Each mycell In myrng If mycell.Interior.Color = vbRed Then datecol = datecol + 1 End If Next mycell Debug.Print datecol End Sub