IF Funtion with coloured celld

You might need to provide a bit more info, are you saying for example that the cells X5 and X9 are colored or are you saying that those cells would contain a word like blue or red?

From my perspective I think there is still more info missing.
For example is there data in B6, B7 and B8 that you are ignoring? Do you want the formula to compare every 4th cell? If there is a word or something in X5 but it isn't a color what should happen then?
So if B5 and B9 have a number if you will - both are 6, and X5 is blue but X9 is blank or has a 10 in it you want the output to be "false" right?
Sometimes an attachment with what you have and a desired outcome is helpful.

I'm not sure that formulas alone can do that. I could be wrong. One of my files changes the color of a cell based on its contents with a function in the VBA editor. You could try that if you're ok doing that.

Wait. So... You want cells B3:F6 to have formulas that state "TRUE" or "FALSE". I've understood that much. Now, when do you want these cells to say "TRUE" and when do you want them to say "FALSE"?

LaffyAffy, looking at it I'm struggling to interpret it but from what I can see...
Can't discern a pattern yet for butter but for "cholesterol lowering" it appears that in B6 it is false because B15 and B18 are both not colored, C6 is false because C16 is colored but C18 is blank, D6 is true because both D15 and D18 are colored.

Ok, so if the colors overlap from row to row anywhere with the same word in column A, it should say true.

I think you've got the pattern I was missing, yes it appears that if an item is colored more than once in the column below it should return true. Of course the poster will have to verify or disagree with that but it seems you've got the pattern.

I still don't see how that's possible with formulas alone. Do you?

LA, I wonder if that too. It might be done with a formula using conditional formatting but that would be a guess as I don't know how to make one that might be that complex. Other might be with visual basic and I have no experience with it. I will keep watching to see if you or someone else might be able to solve this one (and I'll play around with conditional formatting just in case).

esteloulou is it ok with you if I solve this with a public function in VBA

I think it could be doable with a formula under the following conditions.
1) you can add a column that would do a countif to be countif Margarine appears in the column below and how many times.
2) after you count them and the number appears to be greater than 1 you can then incorporate something like a lookup but you would need it to recognize a colored cell (any color would do) and it would have to know that that color needs to appear across from it at least twice in the same column to count as true.
Now, one thing i did find was that you can do a find and replace on a color in a cell. For example I replaced the red cells with CL (for cholesterol lowering), they could be left colored or uncolored.
Now that that was done I'm wondering if the formula might work. Onward.

sounds good im working on it

I'm wondering if this can be tweaked to fit.
first, I added a duplicate of his data to rows 7-10. Then inserted new columns B and C to work with.
I did a find and replace on the colors (some replaced by no color and left some as is, neither matters to what I was doing), then wrote the formula that I'm having trouble getting to work.
=IF(COUNTIF(D$15:D$25,$B7>1),"TRUE","FALSE")
you can see in the attached, I think the sequence is messed up.

Ignore this, its wrong.

Found some errors. Leave that alone.

Here we go. This one seems flawless. Let me know if it works for you. What you need to do now:

1. Copy the code below and paste it into your VBA editor, using Alt + F11.
2. Place the following formula into the top left cell in the area that you wish to have either "TRUE" or "FALSE"

Formula:

Please Login or Register  to view this content.

*B5 stands as only an example cell. It may not be the one you need to use
3. AutoFill to the edges of your earlier said area.

Please Login or Register  to view this content.

I tried using the color of the cell to match the colors, but my program was matching the blank cells as if they were white. So I went with the pattern instead. Hopefully all of your colored cells follow the same pattern lol

Well...yeah. The only lines you would need to change are

Please Login or Register  to view this content.

Whichever column holds the "margarine, butter, cholesterol lowering, etc." needs to replace "A" in the first line and "A7:A" in the second.

If that doesnt make sense, if you want to send me a private message with your real document, you can do that too. If you don't feel comfortable sharing that information, that's ok I can try to explain it on here.

Let's say that the area that you want to say either True or False is now called "E" because I'm tired of retyping that haha. Ok so for each cell inside of E, the formula needs to be

Formula:

Please Login or Register  to view this content.

The X stands for whichever column each specific cell is in. The # stands for whichever row each specific cell is in. So for G6, the formula would be =esteloulou(G6) and for Q267 the formula would be =esteloulou(Q267) does that make sense? Are the cells that currently state "#VALUE" referencing themselves? Or are they referencing other cells?

Ohhhhhhhhhhhhhhhh you didnt tell me that. And no I don't know what that is.

Ok so say E is in Sheet2 and the cells you are referencing are in sheet 1. For each cell in E, the formula now needs to say this:

Formula:

Please Login or Register  to view this content.

Nope, look at the post below. This one is wrong

I think I found the problem. Try this one.

Please Login or Register  to view this content.

Can't test this one, as I don't have the file. But it should work.

Ignore this.

Originally Posted by esteloulou

This is the macros I have now:

esteloulou: Welcome to the Forum, but as a new member please review the forum rules. In particular, when posting code use CODE tags:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Success!!!!!!!! Finally. I tested this one on my own and it works.

Please Login or Register  to view this content.

Haha it's ok. In the sample workbook you uploaded, the values were correct. I don't know what Promo Split Out or Promo Layering looks like, but in the sample workbook, in sheet 1, you had Margarine, Butter, Cholesterol lowering, and Baking. And to the right of it, the cells stated True or False. I can't tell you what to refer to with those cells, because I dont know what the workbook looks like and I don't know where the cells to reference are. Can you either upload a sample workbook that actually looks like your real file or upload pictures of those sheets? Otherwise I don't know what to tell you. And I can't do the whole "Join.me" thing because I'm at work and I'm just helping you with this to pass the time. I'm constantly working on other things so I don't have the time to do that, sorry.

I want to help you but I have no idea what you want to achieve. I can see the purple colors, and I'm guessing that if the purple repeats for one "Sector" then you want some cell to say True. But without the workbook I can't help you, because I have to rewrite the macro above to fit your file. I thought the sample workbook that you posted earlier would be similar to the file you currently have, so I wrote it to work for something that was visually similar. If you can't give it to me, you may have to study the code step by step, figure out what it is doing, and then change it on your own. I'm sorry, but for me to continue working with the resources you have given me is like hunting for buried treasure without a map. I would be happy to finish this job for you if you send me the workbook. If you can, please do so through a private message if the workbook contains important information that you don't want exposed on the internet.

<<<<<<Click my blue name to the left

If you click on his name a box will appear and a choice will include private message, then click on that and you can send LA a private message.

esteloulou, I was hoping you would've emailed me the file by now. I could've worked on it today, but next week I'm off to college, and I won't have the time to help you out anymore. I could try to get back to you when possible, but that might not be for a while. If you need this soon, I hope you find what you are looking for. If not, I will do my best to work on it in the future. Good luck!