HSAR
Banned - Rules violations
- Joined
- Jul 6, 2020
- Messages
- 37
- Office Version
- 2016
- Platform
- Windows
Hi, Team Mrexcel i am new here.
I have been using this below code to count colored cell right now this is just counting colored cell for single column. If i add two or more ranges then its answer would be same rather then different.
I want you to modify this code so that code will count all used range. For Example if it is count Column "J" then result should be mentioned in J1 & if it is count Column "K" then result should be mentioned in K1
and so on till used range.
I am not expecting for formula created by VBA. Please do help.
I have been using this below code to count colored cell right now this is just counting colored cell for single column. If i add two or more ranges then its answer would be same rather then different.
I want you to modify this code so that code will count all used range. For Example if it is count Column "J" then result should be mentioned in J1 & if it is count Column "K" then result should be mentioned in K1
and so on till used range.
I am not expecting for formula created by VBA. Please do help.
VBA Code:
Public Sub CountColorCells()
Dim rng As Range
Dim lColorCounter As Long
Dim rngCell As Range
Set rng = Sheet2.Range("J2:J20")
For Each rngCell In rng
If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(183, 225, 205) Then
lColorCounter = lColorCounter + 1
End If
Next
Sheet2.Range("J1") = lColorCounter
End Sub