Home

Count coloured cells based on conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-807741%22%20slang%3D%22en-US%22%3ECount%20coloured%20cells%20based%20on%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807741%22%20slang%3D%22en-US%22%3ECan%20someone%20help%20me%20please%3F%20I%20am%20trying%20to%20get%20my%20spreadsheet%20to%20count%20cells%20based%20on%20their%20colour%20after%20they%20have%20been%20conditionally%20formatted.%20I%20have%20read%20various%20threads%20on%20here%20and%20made%20some%20progress%20thanks%20to%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3EI%20have%20put%20a%20VDA%20in%20as%20per%20advice%20elsewhere.%20However%2C%20it%20is%20clear%20that%20I%20am%20making%20mistakes%20somewhere.%20I%20have%20attached%20my%20file.%20The%20formula%20is%20put%20in%20next%20to%20the%20red%20cell%20on%20the%20analysis%20tab.%20Any%20help%20greatly%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-807741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%20based%20on%20cell%20colour%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807821%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20coloured%20cells%20based%20on%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392988%22%20target%3D%22_blank%22%3E%40oconnells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20workbook%20is%20not%20macro-enabled%20and%20you%20didn't%20add%20the%20function%20to%20your%20VBA%20environment.%3C%2FP%3E%0A%3CP%3EHowever%2C%20you%20may%20use%20your%20rules%20logic%20in%20formulas%20to%20count%20the%20cells%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(--(%24H%244%3A%24H%24182%26lt%3B%24G%244%3A%24G%24182))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807871%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20coloured%20cells%20based%20on%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392988%22%20target%3D%22_blank%22%3E%40oconnells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyour%20file%20did%20not%20have%20the%20UDF%2C%20as%20also%20pointed%20by%20Sergei.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20attached%20file%2C%20I%20placed%20the%20UDF%20and%20now%20it%20counts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
oconnells
Occasional Contributor
Can someone help me please? I am trying to get my spreadsheet to count cells based on their colour after they have been conditionally formatted. I have read various threads on here and made some progress thanks to @Jamil Mohammad I have put a VDA in as per advice elsewhere. However, it is clear that I am making mistakes somewhere. I have attached my file. The formula is put in next to the red cell on the analysis tab. Any help greatly appreciated.
2 Replies

@oconnells 

Your workbook is not macro-enabled and you didn't add the function to your VBA environment.

However, you may use your rules logic in formulas to count the cells, like

=SUMPRODUCT(--($H$4:$H$182<$G$4:$G$182))

 

@oconnells 

 

Hi,

 

your file did not have the UDF, as also pointed by Sergei.

 

In attached file, I placed the UDF and now it counts.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies