Formula or Function using IF statement on Cell Colors

%3CLINGO-SUB%20id%3D%22lingo-sub-1498143%22%20slang%3D%22en-US%22%3EFormula%20or%20Function%20using%20IF%20statement%20on%20Cell%20Colors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498143%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20a%20problem%20on%20how%20to%20link%20a%20color.%20What%20i%20want%20to%20do%20is%20IF%20Cell%20A1%20or%20A3%20OR%20A5%20is%20filled%20with%20color%20then%20cell%20A10%26nbsp%3Bshould%20have%20a%20Value%20of%201%20then%20if%20A1%20has%20no%20color%20Fill%20but%20A3%20%26amp%3B%20A5%20have%20color%20fill%20then%20A10%20Should%20still%20has%20a%20value%20of%201%20OR%20if%20at%20least%20one%20among%20A1%2CA3%20%26amp%3B%20A5%20has%20color%20fill%20the%20value%20of%20A10%20should%20be%20equal%20to%201%20.%3C%2FP%3E%3CP%3EThen%20if%20A1%2C%20A3%2C%20%26amp%3B%20A5%26nbsp%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ehas%20no%20color%20fill%20the%20value%20of%20A10%20should%20be%20blank.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20help%20me%20guys%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%2091px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202138i8E9F62F772EE5B03%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%2087px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202139i0E43C8A16B92BD6A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.png%22%20style%3D%22width%3A%2086px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202140iE31ECF0D8872A692%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%223.png%22%20alt%3D%223.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1498143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498977%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20or%20Function%20using%20IF%20statement%20on%20Cell%20Colors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F714163%22%20target%3D%22_blank%22%3E%40Taph17%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BAlthough%20there%20are%20ways%20to%20do%20this%2C%20it%20is%20not%20the%20way%20excel%20is%20really%20designed.%26nbsp%3B%20Maybe%20if%20you%20gave%20us%20more%20information%20about%20the%20big%20picture%20we%20could%20suggest%20a%20better%20solution.%26nbsp%3B%20Off%20hand%20right%20now%20I%20would%20suggest%20you%20use%20numbers%20in%20those%20cells%20to%20indicate%20%22on%22%20state%20and%20then%20you%20can%20add%20conditional%20formatting%20to%20%22highlight%22%20them%20if%20needed.%26nbsp%3B%20You%20can%20even%20use%20the%20conditional%20formatting%20rules%20to%20make%20the%20text%20'invisible'%20(i.e.%20same%20color%20as%20background).%3C%2FP%3E%3CP%3EOtherwise%20the%20solutions%20I%20see%20use%20either%20a%20complicated%20trick%20using%20get.cell%20in%20the%20name%20definitions%20or%20VBA.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20went%20ahead%20an%20created%20a%20sample%20sheet%20using%20conditional%20formatting%20I%20mentioned%20above.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22count%20fill.xlsx%20-%20Saved%206_30_2020%2012_08_01%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202204i1E77D062FA0166CF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22count%20fill.xlsx%20-%20Saved%206_30_2020%2012_08_01%20PM.png%22%20alt%3D%22count%20fill.xlsx%20-%20Saved%206_30_2020%2012_08_01%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500460%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20or%20Function%20using%20IF%20statement%20on%20Cell%20Colors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%2C%20Got%20it%20sir%2C%20i%20will%20try%20conditional%20formatting%2C%20if%20it%20doesn't%20work%2C%20i%20will%20try%20to%20provide%20a%20broader%20explanation%20of%20my%20problem.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

I'm having a problem on how to link a color. What i want to do is IF Cell A1 or A3 OR A5 is filled with color then cell A10 should have a Value of 1 then if A1 has no color Fill but A3 & A5 have color fill then A10 Should still has a value of 1 OR if at least one among A1,A3 & A5 has color fill the value of A10 should be equal to 1 .

Then if A1, A3, & A5  has no color fill the value of A10 should be blank. 

 

Hope you help me guys

Thank you

 

 

 

1.png2.png3.png

2 Replies

@Taph17   Although there are ways to do this, it is not the way excel is really designed.  Maybe if you gave us more information about the big picture we could suggest a better solution.  Off hand right now I would suggest you use numbers in those cells to indicate "on" state and then you can add conditional formatting to "highlight" them if needed.  You can even use the conditional formatting rules to make the text 'invisible' (i.e. same color as background).

Otherwise the solutions I see use either a complicated trick using get.cell in the name definitions or VBA. 

I went ahead an created a sample sheet using conditional formatting I mentioned above.

count fill.xlsx - Saved 6_30_2020 12_08_01 PM.png

@mtarler, Got it sir, i will try conditional formatting, if it doesn't work, i will try to provide a broader explanation of my problem. Thank you