Creating Loop for Entire Used Range across all sheets

[HSAR]

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.

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

 

[mumps]

Glad it worked out.

 

[DanteAmor]

DanteAmor

You are really a great man thank you again for this favor.

I'm glad to help you. Thanks for the feedback.