IF Statement in Excel Based on Cell Colour

[@pierreshen]

A note for people using Excel (or Office) in a locale that uses commas as decimal separator, don't forget to use semi-colon in the GET.CELL call to avoid "#value" errors :) Thanks for the tip that was really helpful!

[@TheSeeparag]

I just saw your once video and simply loved it for the simplicity and for its ability to solve the problem. Then I had another problem I searched for solution and guess what... your video (this) popped up again.... needless to say it was exactly what I was looking for.... and then without delaying I subscribed for the channel. Absolute appreciate your work....

[@vernikad5061]

Thank you!!!!! You saved me hours of manual work to look for specifically colored cells in large file.

[@sarvasan]

Spent days searching blogs and vids before i found your "work arounds" - Well done!! For those finding that other cells not being updated, I found that if your press the "filter" pull down and press OK, it 'refreshers' & updates the other cells... worth a try?

[@CaspianKR]

Is there a way to make this work with conditional formatting colours? The ifstatements don't seem to like conditional format colors

[@mishamulyakaev7406]

Exactly what I needed! KZbin is the best education... Thank you

[@TheReal_Dave]

Super useful! Nice video. Rather than the format painter, I just get in the habit of hitting Ctrl-Alt-F9 if I update any of the shading/fill colors. Ctrl-Alt-F9 tells Excel to recalculate all of the formulas inclusive of that property.

[@julias7332]

Great tutorial, does exactly what I needed & saved me a lot of time & brain power. It works perfectly with the style colours that I have used on over 3 years worth of data & now want to use for another output/purpose, it even refreshes automatically when I apply or un-apply the style colour, thank you 😎

[@brabreborbisia9924]

thank you thank you thank you thank you! I have been looking for a way to do this on and off for the past 5 years. Thank you again!

[@rmf1979-j2b]

Wow - that is an amazing trick. I have bookmarked this for future use and thank you very much for your free tutorial. I will DEFINITELY use this again!

[@ChesterTugwell]

You are welcome!

[@jaandel1]

​@@ChesterTugwellhow can extract color code using the contional formating.. this just work if you actually use the color only

[@djvicks21]

Thanks Chester. This is a brilliant tutorial. But I tried this and realized this doesnt work on cell colored using conditional formatting. Is there a workaround to that?

[@tuckernorris7479]

Did you ever find a solution to this?

[@TrueSpeaker-gx5tw]

I would be interested in that as well

[@Berquin]

I would be interested in that as well. At least knowing if it is possible using conditional formating.

[@ElishaPlaytime]

Hi, Can you help me please, I have 2 sheet, the other sheet is to copy the main sheet, but I want to make it not copy the data from the main if the cell color is gray. can you help me how to do it please.. Thanks

[@marctemura2017]

Chester that is some great outside the box thinking. Keep up great work. 👍⭐

[@johngreen1283]

Very helpful. This turned an impossible task into a simple one.

[@advent7324]

Great and useful tip Chester. Thanks

[@cadcar13]

This is awesome, thank you! I've been trying to solve this problem for months! Appreciate you sharing this info!

[@lisaraley6904]

Thank you SO much for putting together this video. Fantastic!!!

[@nye1762]

Can you uss this function in connection with a cell that is conditionally formatted? I have a column of record ID with corresponding where duplicate IDs are conditionally formatted. I need an IF statement to find the cells conditionall formatted and extract the corresponding value. The method here does not recognise the conditional formatting of duplicate cells. is there a way?

[@nitiwat_a]

Did you find the solution? I am struggling with the same situation.

[@pablomonterroso7135]

I have the same problem... Do y'all find a solution?

[@DragonsTaco]

This is fantastic, works great! I used the concept to create a table that used row color to group together rows with the same key values to make it more legible. I've tried every VBA method to do this before and while it would work it is so much more complicated than this. It gives me all kinds of ideas to use the Name Manager in the future. Thanks for the short but really valuable lesson! 💪

[@rich_banana5451]

You are actually the greatest of all time

[@TeamHuanimal]

took me forever to find this, thank you so much for the video!!!

[@priyachandrasena7729]

Thanks very much for the tutorial !

[@deghazbalsa]

Thank you very much Chester........ you make my work much easier......

[@spacebasedsolarpowersbspsb6548]

Excellent... Very step by step explanations... Keep up the good work bro..... Thanks thanks thanks....

[@Mugen89K]

Hello, sorry if you answered this already, but can you do the same but with FONT color instead of cell color? thank you!!

[@jeannesarfatyglazer5005]

This was incredibly useful. Thank you so much for sharing!

[@jacquiboyce2216]

Brilliant!! This is exactly what I was looking for!

[@adammiller2914]

Excellent tutorial, this saved me a great deal of manual sorting!

[@chongandrew5498]

Is it possible a vbn code to refresh calculation of a vbn created formula? thanks

[@Msedk]

Works wonders. Thanks a million for this little trick. Makes a world of difference. :-)

[@ChesterTugwell]

You're welcome!

[@PastaFazool4266]

Outstanding! Thank you!

[@anmac6910]

How do I use a coloured cells's value itself to highlight secondary duplicates like finding colpured cells value first then using that value to highlight another set of duplicates? Kinda like highlighting secondary uplicates after finding the first

[@ngunghuong6938]

Thank you so much, this is so helpful to me.

[@jiricapek2603]

Thank you! Very useful....!!! Appriciated

[@GhotiTehFish]

Thanks man, saved me some time!

[@mkparker99]

Brilliant! Very helpful!

[@ChesterTugwell]

Glad to hear it!

[@xyclos]

Fanstastic!! Thanks a lot!!!

[@DavidAFleming]

Do you know if you can do this with Conditional Formatting? The tip works great for fields that have been coloured by hand, but i can't get it to work for Conditional cells.

[@ChesterTugwell]

Hi Dave. It won't work with conditional formatting unfortunately. However, the conditional formatting must be based on a test, so all you do is use the same test in the IF statement. No need to use colour as your IF test.

[@danc30nsuns3t]

@@ChesterTugwell How can this be done when your test is for duplicate values?

[@hollyclark3731]

@@ChesterTugwell I am trying very hard how to figure this out with an IF Top 5. Any pointers or other videos you've done that may be helpful?

[@15ti_Maj_23.]

Great work!

[@michaelvictor9107]

Thank you so much🙏🙏worked like a charm

[@edgarslapins3995]

Sadly doesn't work on google sheets, any recommendations? Thanks

[@875jaxxy]

Thanks for this tutorial

[@annamccutcheon-qt8iv]

Thanks for this. I have tried it but the value I get for all cell colours is zero. Have followed step for step. Any chance the code for cell colour in get.cell has changed from 38? I am using excel (MS Office suite) on a Mac. Any chance that is why it isn't working for me? Thanks

[@nikhilnayak9700]

Hi Chester. Thank you for this, another brilliant workaround. I have a question regarding Colors in conditional formatting. This approach doesn’t seem to capture the cell background Color for those cells that have been formatted by a condition. Is there a way to capture these as well?

[@kevinmcaulay964]

This was the exact question I was coming on ti ask. Did you ever get a response?

[@augustinestephensteve7rock327]

God bless you.. this works perfectly.

[@adventuresofmplusr]

Is there anyway to do the same thing for font color instead of cell color?

[@barden82]

Cheers for this!

[@mikepope666]

Works like a charm... Thanks!

[@haseebkhalid7963]

Brilliant. Saved a lot of time..

[@rajeshshah6758]

Thanks Chester. This is what I have been looking for. You just made my work very easy.

[@mbowick123]

this is so helpful!!! thank you :)

[@habibsicttutorials6684]

Nicely done.

[@ChesterTugwell]

Thanks!

[@jasonlamsen]

Hi, thanks for the video! I have a problem same with others, Everytime I drag down the cell it only returns 1 value (based on the original cell where its color is based on). I already removed the'$' on the formula.

[@joebaxter2159]

Excellent. Thank you!

[@laurenluo1462]

Thank you Chester! I tried your method, but regardless what color I have, when I type in "=CELLCOLOUR", it returns the same value to every single color. For example, I had cells in yellow, green and red, but number 6 is returned for all the colors. Would you please advise on this? thank you very much!

[@widepootis]

have you saved your file as a macro-enabled worksheet? that worked for me as soon as I did that

[@hasan_zafaranchi]

very good, thanks a lot, I have a problem on this: when I save it and close the excel and open that file again, the defined formula doesn't work :( would you help me please. ( I save in xlsm format ) xlsx also doesn't work.

[@lkrizo6448]

Hi. Could you make a formula - if you change the color of cell, next cell to it, will show todays date. Thank you

[@georgiivanov1902]

Thank you for the tip. Saved me lots of time!

[@AlexiaBoubouli]

Dear Chester, thank you for the video, I have problem getting different values for each colour. If the cell has a value and then i delete the value refresh the cell then only it shows different numbers, if I drag down the formula it does not work, is there something you would recommend? thank you

[@fabiobordignon3840]

Good stuff, thanks!!!

[@KateFeldman-q6d]

love it, thanks so much, it helps a lot

[@ahdonahd]

Does it work with cell colored by condition effect?

[@gabesteinert9545]

I have no name manager tab. Any suggestions as what to do??

[@drwumaths9862]

This is fab! It works on the pre-set colours. I tried to use it on conditional formatted colours it doesn't quite work. For example, if I colour a cell grey, it returns "15"; but if the cell has no pre-set colour but was conditional-formatted as grey, this formula returns "0", same value as other cells that are the blank default cell colour. Do you know a way to obtain the colour codes for the conditional formatted colours?

[@ColorCopiesUSA]

This was geniously done! Thanks

[@wanrids]

hai. where can i get the GET.CELL lisp?

[@sajjadabouei6721]

OG thank you man

[@thiagoleite1989]

Thank you for the tutorial! However I noticed that if the color comes from conditional formatting (for example for duplicate amounts) the get.colour won't work :/ Any way around that?

[@antjon078]

How did you find out that 38 equals the colour of the cell? Is there a list anywhere because I have been looking around and I can't find anything.

[@bluesage1528]

This is VERY creative!

[@ravillatorom]

It works great, but want to use the workbook as a templete. Cleared all colors back to white cells, but even if the colored field is back to white, the cel color formula keeps naming the wrong color. Is like the formula doesn't auto refresh. I tried the refresh icon, but it didn't update either

[@kellybollman4034]

This is amazing! Can this be used to count the number of cells in a row that are the particular target background color? If so, how?

[@mmmaurox]

i have the same question

[@cindymiller4988]

You should be able to do the same thing except replace the countif, for the if statement.

[@SamehMustafa007]

Perfect so Brilliant idea👍👍👍

[@shivabalan3255]

Thank you for your help sir

[@nikolabatinic6675]

I wanted to ask is this IF funtion is possible to use in a range with COUNTIF (i need to have 2 collors (red or green) and cell are collored and have one number ih them from 1 to 15) I wish to tr y to get info: if collor of cell is red and have number 1 in it to count howmuch number 1 is in red cell and howmuch number 1 is in green cells ? Thanks :)

[@davidsyes5970]

In Excel, without using get.cell, is it possible to in, say, B1 use =CELL("BACKGROUNDCOLOR", B1) or some CELLINFO variations? It might take hitting F9 or CTRL+F9 to refresh, unless the formatting painter triggers it. If that much works, then, the user next needs to standardize on a small subset of colors that make color wheel sense on screen and on paper, and put them into a grid. Apply the technique of =CELL("BACKGROUNDCOLOR", (location of first subset color). Then, use INDEX and MATCH to embed the color lookup in a formula that is doing the SUMIF and COUNTIF stuff. I don't know if Excel color names are out there, but if so, another implementation could take the color name and apply it to an adjacent cell, then the colorized cell could be used against the data to apply colors or to count/sum values automatically painted by yet another LOOKUP/VLOOKUP/HLOOKUP/INDEX+MATCH stuff. (I do this but, not in Excel. I still use old, end-of-life, no-longer-supported Lotus 1-2-3 and the @SETSTYLE add-in from year 2005. It drives conditional formatting from cells, like regular formulas, not via any Conditional Formatting GUI. But, even if in Excel someone writes a Visual Basic script, it might still be necessary to save the file as a macro enabled file, which might be a positive or a negative, depending on the users involved.) Good luck!

[@naifalismail815]

Thank you, saved a lot of time

[@mikelennon1078]

Lets say I have 1000 records but only certain row id's are colored. I would like to have a column which find the next colored ID and give it an incremental number. i.e. the first colored id is in row 10, the second row 12, the third in row 40. And I would like to get a 1,2,3,...... in front of row 10, row 12, row 40, ................... respectively. Would you please let me know how this can be done? I would appreciate it.

[@mirceavuscan4706]

Do you think it is possible to run a macro that will colour cells from a previous file in the actual one? There are 3 colours and the criteria are some concatenate values

[@premjeetgupta90]

Thanks Chester, Does this function work with conditional formatting colour. As I have tired but it is not showing any no. Thanks.

[@nataliewinn1695]

Would love to know an answer to this, I’ve tried it as well!

[@ChesterTugwell]

Unfortunately not :-(

[@DenisFourie]

Is there a way to do exactly that, but based on the TEXT colour instead of the CELL colour? Thanks for the info.

[@kerongranger1137]

asking the same question

[@geoffreymallett2295]

This is awesome. Thanks for the help!

[@yuqinghuang5891]

Hey Chester! Thanks for your sharing! I am able to apply your function on most of my excel files. However, it returned #BLOCKED! when I applied same function on one tab in an excel. But it works perfectly fine for other tabs in same excel files. Do you have any clue? Thanks!

[@EyeIn_The_Sky]

is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True"?

[@erlanggahawal85]

Brilliant Idea. I'm sorry if i dont see your video fully or another your video. how we set this formula (an IF based on cell colour), but in vertical area? because your formula is working through horizontal area. thanks

[@26jasti]

Hi...I tried the same thing, and it is not working. My colored cells - are conditionally formatted colored cells. Does that cause a problem?

[@marcins5235]

Ciao Chester, I've followed your instructions and when I enter the name in cell I see #NAME? error - what can be wrong?

[@hossammaarek]

Fantastic .. THANK YOU

[@dfinkel22]

Great job!

[@rgaskins310]

This works well if the cell is directly colored, but if the cell is conditionally formatted, it does not work. Can you provide any assistance for that?

[@kempisabel9945]

life saver!! thank you!!

[@RockyBevo]

Thanks Brotha!

[@AdaLovesLife]

Really good! Thank you for the great video!

[@ChesterTugwell]

Glad you liked it!

[@mohamedmahmoudabdelsalamel990]

Hi, Thank you ..... Could you help me please i have worksheet with several repeated names every name have any of 3 colors Red, Green or Blue i want count a specific name with the a specific color for ex. "Name1" repeated with different colors so i want to count how many Red, Green or Blue of that name , THANKS

[@dhunpagla3871]

Thanks Mr Tugwell...awesome 👌

[@himanshubhatt1783]

Hi, thanks for the simple solution. Just to add, the same file should work with Binary format also.

[@rosimarhernandez8572]

I’m loving all your tutorials Chester! Thank you so much for explaining things so well 🤗. I created a color coded, 8 items, drop down list in one of the cells and I would love to make the cells to the right side of this one to take on the same color/pattern (achieved with conditional formatting) of the selection of that cell on the left. The right side columns have their own drop down lists but I made them colorless in order to duplicate the colors/patterns contained in the other one. Is this even achievable?

[@Jess_Smith0903]

Is there any formula that I can use for this case: if cell X is highlighted in any color, then cell Z equals “blank”?

[@tbrizzo]

Nice video Chester! I was actually looking for another kind of function, can you help me out? I want to return the cell's text on a given interval, if that cell have a specific color. For example, if only one cell from A1 to A10 is red, i want the function to return whatever is written in that red cell. Is it possible? Can you help me out?

[@PreetiSharma-xw5yl]

What if I want to return a text or column name instead of a number?