Changing an empty cell's background color depending on a different cell value.

[Excell4Dummies]

Hi there!

I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..

I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.

I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.

Any help will be greatly appreciated. And thanks for your time in advance! I am hoping to learn this so next time I can change the code myself regardless of the different case requirement.

[simarui]

sounds to me like conditional formatting should do the trick...

you'll need to create a different rule for each team you might put in B11 (5 rules in total) the formula is simply:

Please Login or Register  to view this content.

and it applies to:

Please Login or Register  to view this content.

same story with the months, but the formula will be something like:

Please Login or Register  to view this content.

let me know if you have questions and / or upload a sample file and i'll just do it for you.

[Excell4Dummies]

Hi Simarui, thank you for your prompt reply! I actually got the border to finally work - so happy about it... However, I cannot get the cell fill to change based on the date. I believe there is something wrong with the formatting two conditions under one umbrella: month()>3 + month()<=6 . The color doesn't change when you input a new date...

Can you please look at my sample formatting and see if you can catch the mistake? Thanks in advance.

[newdoverman]

The attached has some of the Conditional Formatting that you want. Adjust to suit your requirements and add the others following the examples for the ones given.

[simarui]

i'm sure newdoverman's file works (didn't d/l it...) but i've also fixed the one you uploaded...

gotta use and() to have two conditions, and instead of + use a comma.

[Excell4Dummies]

Thank you to both of you for all your help! Glad I learned something new today .

[newdoverman]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.