hashibhai111 said:
Guys Assalam o Alikum!
Muja ya pochna ha kai! agar kuch rows excel mai colour kar di jaein to kya unko count kiya ja skta ha?
mean
10 rows hain, mai 5 diffent rows ko Green colour da raha haoin!
mai ab un 5 rows ko aik cell mai dekhta chahta ho ka wo show kr da ka total mai
5 rows green hain!
Thanks!
1. Press Alt+F11
2. Insert k Menu m Module select karain aur ya code paste kar dain.
Code:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
I assume k ap ki A1 sa A10 tak rows color hain. Tu ap ya formula istimal karain. Lekan ap ko ya karna pare ga k C1 ko bhe wohi color karna pare ga jo k ap count karna chahte hain, for example agar ap green rows ko color karna chahte hain tu c1 ko bhee green karna pare ga.
Code:
=ColorFunction(C1,A2:A12,FALSE)
Regards,
Bookmarks