Home

Counting cells based on their colors given from conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-759071%22%20slang%3D%22en-US%22%3ECounting%20cells%20based%20on%20their%20colors%20given%20from%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759071%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20multiple%20file%20sheets%20that%20contain%20multiple%20conditional%20formatting%20rules%20based%20from%20formulas.%20Each%20row%20is%20completely%20highlighted%20and%20I%20want%20to%20count%20the%20number%20of%20rows%20that%20are%20highlighted%20in%20a%20specific%20color%20that's%20decided%20by%20the%20conditional%20formatting.%20I%20have%20done%20some%20research%20and%20have%20seen%20things%20about%20VBA%20and%20macros%20working%2C%20but%20I%20haven't%20been%20able%20to%20get%20anything%20to%20work.%20Can%20anyone%20help%20me%20so%20I%20can%20use%20this%20across%20multiple%20sheets%20and%20it%20can%20get%20reran%20if%20more%20data%20is%20added%20or%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759071%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759122%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20cells%20based%20on%20their%20colors%20given%20from%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377696%22%20target%3D%22_blank%22%3E%40FoxJacob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jacob%2C%3C%2FP%3E%3CP%3Ethe%20following%20macro%20counts%20all%20weekends%20in%20selection.%20Here%20the%20colorindex%20is%2043.%20See%20attachment%2C%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20ReadColor()%3CBR%20%2F%3EDim%20rngcell%20As%20Range%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20rngcell%20In%20Selection%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20rngcell.DisplayFormat.Interior.ColorIndex%20%3D%2043%20Then%3CBR%20%2F%3Ei%20%3D%20i%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20rngcell%3CBR%20%2F%3EMsgBox%20i%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3EBest%20regards%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EBernd%3C%2FPRE%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fhttps%3A%5C%5Cwww.vba-Tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759176%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20cells%20based%20on%20their%20colors%20given%20from%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759176%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20doing%20something%20similar%3A%20conditional%20formatting%20in%20colour%20then%20trying%20to%20count%20the%20cells%20so%20colored.%20The%20formatting%26nbsp%3B%20%26nbsp%3Bapparently%20works%20as%20the%20right%20cells%20get%20colored.%3C%2FP%3E%3CP%3EBut%20The%20counting%20has%20a%20problem%3A%3C%2FP%3E%3CP%3EWhile%20the%20VBA%20thing%20(linked%26nbsp%3B%20below)%20works%20fine%20on%20cells%20manually%20colored%26nbsp%3B%20it%20does%20not%20see%20see%20the%20cells%20colored%26nbsp%3B%20by%20the%20conditionnal%20method.%20However%2C%20It%20is%20not%20an%20issue%20with%20the%20VBA%20thing%20since%26nbsp%3B%20even%20basic%20functions%20such%20as%20manual%20filling%20for%20instance%20also%20behave%20as%20if%20the%20cells%20had%20not%20already%20been%20colored%20by%20the%20conditionnal%20protocol.%20For%20some%20reason%20their%20conditionally%20attributed%20format%20is%20not%20recognised%20by%20Excel%20itself.%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20myself%20and%20Jacob%20would%20be%20grateful%20to%20get%20some%20help%20about%20overcoming%26nbsp%3B%20this%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(My%20point%20for%20Jacob%20is%20that%26nbsp%3B%20VBA%20thing%20may%20not%20be%20at%20fault%2C%20try%20it%20on%20manually%20colored%20cells%20and%20you%20will%20see%20that%20it%20works.%20It%20will%20fail%20specifically%20on%20conditionnally%20colored%20cells%20as%20explained%20above)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Foffice-developer%2Fcount-cells-number-with-color-using-vba%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Foffice-developer%2Fcount-cells-number-with-color-using-vba%20%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759537%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20cells%20based%20on%20their%20colors%20given%20from%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377696%22%20target%3D%22_blank%22%3E%40FoxJacob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20follow%20up%20on%20my%20last%20post.%20I%20realized%20why%20the%20count%20isn't%20coming%20out%20properly.%20The%20formula%20I%20have%20for%26nbsp%3B%20my%20cond.%20formatting%20takes%20precedence%20over%20another%20one.%20So%20the%20function%20is%20reading%20cells%20as%20if%20they%20had%20the%20color%20code%20for%20one%20color%2C%20but%20then%20when%20searched%20for%20another%2C%20it%20counts%20them%20as%20well.%20So%20basically%20the%20code%20I%20posted%20before%20is%20reading%20the%20cell%20as%20having%20two%20color%20codes.%20How%20can%20I%20get%20the%20formula%20to%20read%20one%20single%20color%20code%2C%20the%20one%20that%20is%20showing%20on%20the%20document%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759492%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20cells%20based%20on%20their%20colors%20given%20from%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377694%22%20target%3D%22_blank%22%3E%40Philippe62%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20CountColorCells(CellsRange%20As%20Range%2C%20ColorRng%20As%20Range)%3CBR%20%2F%3EDim%20Bambo%20As%20Boolean%3CBR%20%2F%3EDim%20dbw%20As%20String%3CBR%20%2F%3EDim%20CFCELL%20As%20Range%3CBR%20%2F%3EDim%20CF1%20As%20Single%3CBR%20%2F%3EDim%20CF2%20As%20Double%3CBR%20%2F%3EDim%20CF3%20As%20Long%3CBR%20%2F%3EBambo%20%3D%20False%3CBR%20%2F%3EFor%20CF1%20%3D%201%20To%20CellsRange.FormatConditions.Count%3CBR%20%2F%3EIf%20CellsRange.FormatConditions(CF1).Interior.ColorIndex%20%3D%20ColorRng.Interior.ColorIndex%20Then%3CBR%20%2F%3EBambo%20%3D%20True%3CBR%20%2F%3EExit%20For%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20CF1%3CBR%20%2F%3ECF2%20%3D%200%3CBR%20%2F%3ECF3%20%3D%200%3CBR%20%2F%3EIf%20Bambo%20%3D%20True%20Then%3CBR%20%2F%3EFor%20Each%20CFCELL%20In%20CellsRange%3CBR%20%2F%3Edbw%20%3D%20CFCELL.FormatConditions(CF1).Formula1%3CBR%20%2F%3Edbw%20%3D%20Application.ConvertFormula(dbw%2C%20xlA1%2C%20xlR1C1)%3CBR%20%2F%3Edbw%20%3D%20Application.ConvertFormula(dbw%2C%20xlR1C1%2C%20xlA1%2C%20%2C%20ActiveCell.Resize(CellsRange.Rows.Count%2C%20CellsRange.Columns.Count).Cells(CF3%20%2B%201))%3CBR%20%2F%3EIf%20Evaluate(dbw)%20%3D%20True%20Then%20CF2%20%3D%20CF2%20%2B%201%3CBR%20%2F%3ECF3%20%3D%20CF3%20%2B%201%3CBR%20%2F%3ENext%20CFCELL%3CBR%20%2F%3EElse%3CBR%20%2F%3ECountColorCells%20%3D%20%22NO-COLOR%22%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ECountColorCells%20%3D%20CF2%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20VBA%20code%20im%20using%20to%20create%20the%20function%2C%20but%20when%20I%20run%20the%20function%20in%20my%20spreadsheet%20it%20doesn't%20count%20properly.%20I'm%20not%20sure%20if%20its%20a%20conditional%20formatting%20issue%20or%20and%20issue%20with%20the%20code%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

I have multiple file sheets that contain multiple conditional formatting rules based from formulas. Each row is completely highlighted and I want to count the number of rows that are highlighted in a specific color that's decided by the conditional formatting. I have done some research and have seen things about VBA and macros working, but I haven't been able to get anything to work. Can anyone help me so I can use this across multiple sheets and it can get reran if more data is added or changed.

 

Thanks!

4 Replies

@Deleted 

Hi Jacob,

the following macro counts all weekends in selection. Here the colorindex is 43. See attachment, too.

 

Sub ReadColor()
Dim rngcell As Range
Dim i As Integer

For Each rngcell In Selection

If rngcell.DisplayFormat.Interior.ColorIndex = 43 Then
i = i + 1
End If
Next rngcell
MsgBox i
End Sub
Best regards 

Bernd

www.vba-Tanker.com

I am doing something similar: conditional formatting in colour then trying to count the cells so colored. The formatting   apparently works as the right cells get colored.

But The counting has a problem:

While the VBA thing (linked  below) works fine on cells manually colored  it does not see see the cells colored  by the conditionnal method. However, It is not an issue with the VBA thing since  even basic functions such as manual filling for instance also behave as if the cells had not already been colored by the conditionnal protocol. For some reason their conditionally attributed format is not recognised by Excel itself. 

Both myself and Jacob would be grateful to get some help about overcoming  this issue. 

 

(My point for Jacob is that  VBA thing may not be at fault, try it on manually colored cells and you will see that it works. It will fail specifically on conditionnally colored cells as explained above)

  https://docs.microsoft.com/en-us/office/troubleshoot/office-developer/count-cells-number-with-color-...

@Philippe62

 

Function CountColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
CountColorCells = "NO-COLOR"
Exit Function
End If
CountColorCells = CF2
End Function

 

This is the VBA code im using to create the function, but when I run the function in my spreadsheet it doesn't count properly. I'm not sure if its a conditional formatting issue or and issue with the code

 

@Deleted 

 

To follow up on my last post. I realized why the count isn't coming out properly. The formula I have for  my cond. formatting takes precedence over another one. So the function is reading cells as if they had the color code for one color, but then when searched for another, it counts them as well. So basically the code I posted before is reading the cell as having two color codes. How can I get the formula to read one single color code, the one that is showing on the document??

 

Thanks

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies