Home

Conditional Formatting - Color Fill

%3CLINGO-SUB%20id%3D%22lingo-sub-677025%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677025%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20set%20up%20a%20conditional%20format%20to%20change%20the%20color%20of%20a%20cell%20based%20on%20the%20value%20of%20it.%20The%20value%20is%20Yes%2FNo%20and%20the%20format%20is%20to%20fill%20the%20cells%20Green%2FRed.%20This%20works%20fine%2C%20however%20excel%20doesn't%20recognize%20the%20cells%20as%20being%20filled.%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20picture%2C%20where%20a%20cell%20is%20selected%20(highlighted%20in%20yellow)%20and%20the%20fill%20is%20set%20to%20none.%20However%2C%20the%20cell%20is%20actually%20green.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Thanks%2C%3C%2FP%3E%3CP%3EHarry%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-677025%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677149%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356385%22%20target%3D%22_blank%22%3E%40HarryE%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EConditional%20formatting%20overwrites%20cell%20color%20format.%20It%20doesn't%20matter%20what%20do%20you%20have%20in%20Fill.%20The%20latest%20will%20be%20applied%20only%20to%20the%20cells%20for%20which%20conditional%20formatting%20doesn't%20trigger%20format%20changing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677177%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20quick%20reply%2C%20the%20end%20goal%20of%20what%20i'm%20trying%20to%20do%20is%20set%20up%20a%20live%20count%20of%20which%20cells%20are%20red%20and%20which%20are%20green.%3C%2FP%3E%3CP%3ESo%20I%20have%20my%20conditional%20formatting%20set%20up%20to%20color%20the%20cells%20based%20on%20the%20value%20and%20then%20i%20can%20use%20a%20%22CountCellsByColor%22%20formula%20to%20count%20the%20no.%20of%20red%20vs%20green.%20However%20when%20conditional%20formatting%20is%20applied%20this%20formula%20doesn't%20work%20because%20it%20reads%20what%20color%20the%20cells%20is%20filled%20with.%20Which%20with%20conditional%20formatting%20it%20is%20classed%20as%20no%20fill.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20there%20was%20a%20way%20to%20make%20conditional%20formatting%20actually%20fill%20the%20cell%20and%20change%20it%20as%20normal%20based%20on%20the%20value%2C%20so%20that%20the%20formula%20works%20and%20I%20could%20have%20a%20live%20feed%20of%20what%20the%20count%20is.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%2C%20any%20pointers%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHarry%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677243%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356385%22%20target%3D%22_blank%22%3E%40HarryE%3C%2FA%3EIs%20there%20a%20reason%20you%20want%20to%20do%20this%20indirectly%20via%20the%20colour%3F%20Why%20not%20use%20a%20COUNTIF%20function%20and%20directly%20count%20how%20many%20of%20the%20cells%20are%20True%20and%20how%20many%20are%20False%2C%20or%20Yes%20vs%20No%2C%20or%20whatever%20the%20underlying%20data%20is%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677253%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356439%22%20target%3D%22_blank%22%3E%40AdamV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Adam%2C%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20underlying%20data%20varies%2C%20the%20content%20is%20different.%3C%2FP%3E%3CP%3ESo%20the%20color%20is%20a%20way%20of%20presenting%20if%20the%20data%2Fanswer%20is%20correct%2Fincorrect%20-%20Green%2FRed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20will%20look%20into%20it%20to%20see%20if%20there%20is%20a%20way%20round%20using%20this%20methods%2C%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EHarry%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677255%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356385%22%20target%3D%22_blank%22%3E%40HarryE%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%20at%20least%20couple%20of%20threads%20how%20to%20do%20calculations%20based%20on%20conditional%20formatting%20color%20(VBA).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20the%20same%20time%20why%20don't%20you%20COUNTIFS()%20all%20Yes%20and%20No%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677264%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%2C%20will%20try%20this%20now%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677284%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356385%22%20target%3D%22_blank%22%3E%40HarryE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Harry%2C%3C%2FP%3E%3CP%3Ethere%20is%20a%20way%20with%20vba%20to%20do%20this%3A%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%3E%3CBR%20%2F%3EFor%20Each%20rngcell%20In%20Selection%3CBR%20%2F%3E%20Debug.Print%20%22cell-color%3A%20%22%20%26amp%3B%20rngcell.Interior.ColorIndex%3CBR%20%2F%3E%20Debug.Print%20%22cell-color%20cond.%20format%3A%20%22%20%26amp%3B%20rngcell.DisplayFormat.Interior.ColorIndex%3CBR%20%2F%3ENext%20rngcell%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBa-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%20-%20a%20databse%20full%20of%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677305%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Color%20Fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677305%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%2C%20at%20an%20initial%20look%20it%20seems%20to%20work.%20Will%20confirm%20soon!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%20Harry%3C%2FP%3E%3C%2FLINGO-BODY%3E
HarryE
Occasional Contributor

Hi,

 

I have set up a conditional format to change the color of a cell based on the value of it. The value is Yes/No and the format is to fill the cells Green/Red. This works fine, however excel doesn't recognize the cells as being filled. 

See the picture, where a cell is selected (highlighted in yellow) and the fill is set to none. However, the cell is actually green. 

 

Many Thanks,

Harry 

8 Replies

@HarryE ,

Conditional formatting overwrites cell color format. It doesn't matter what do you have in Fill. The latest will be applied only to the cells for which conditional formatting doesn't trigger format changing.

@Sergei Baklan 

 

Thanks for your quick reply, the end goal of what i'm trying to do is set up a live count of which cells are red and which are green.

So I have my conditional formatting set up to color the cells based on the value and then i can use a "CountCellsByColor" formula to count the no. of red vs green. However when conditional formatting is applied this formula doesn't work because it reads what color the cells is filled with. Which with conditional formatting it is classed as no fill. 

I was hoping there was a way to make conditional formatting actually fill the cell and change it as normal based on the value, so that the formula works and I could have a live feed of what the count is. 

 

Thanks for your help, any pointers appreciated!

 

Harry 

@HarryEIs there a reason you want to do this indirectly via the colour? Why not use a COUNTIF function and directly count how many of the cells are True and how many are False, or Yes vs No, or whatever the underlying data is?

@AdamV 

 

Hi Adam, 

The underlying data varies, the content is different.

So the color is a way of presenting if the data/answer is correct/incorrect - Green/Red

 

But I will look into it to see if there is a way round using this methods, thanks!

 

Regards

Harry

@HarryE ,

 

Here are at least couple of threads how to do calculations based on conditional formatting color (VBA).

 

At the same time why don't you COUNTIFS() all Yes and No?

@Sergei Baklan 

 

Thanks for the reply, will try this now :)

 

@HarryE 

Hi Harry,

there is a way with vba to do this: see attachment, too.

 

Sub ReadColor()
Dim rngcell As Range

For Each rngcell In Selection
Debug.Print "cell-color: " & rngcell.Interior.ColorIndex
Debug.Print "cell-color cond. format: " & rngcell.DisplayFormat.Interior.ColorIndex
Next rngcell

End Sub

 

Best regards from germany

Bernd

www.vba-Tanker.com - a databse full of macros

 @Berndvbatanker 

 

Thank you very much, at an initial look it seems to work. Will confirm soon!

 

Regards Harry

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