Specific IF formulas based on background color

[nchiari]

Hello guys! Really noob VBA and macro user here! I need your help for something I have to do at my work to make it more simple and fast for me. I'll try to explain myself the best I can, sorry if I make this overly complicated, that's not my intention, but I have many variables to consider, so here I go.

I created an example table to illustrate the possible cases:

You are looking at a 5-day worksheet of John Doe, Jane Doe and Sam Doe, three members of our staff.

Long explaining (select below to read):

As you can see, John only worked at day 1, day 3 and day 5. At day 2 and day 4, John missed work without warning. So I MANUALLY paint those days he missed in yellow, as well as the name cell, so I can have a quick reference if he missed ANY day at work. The column G has a COUNTA function that tells me how many days John actually worked (in this case, only 3). The colum H represents a "" value that we give to the staff members that don't miss ANY day at work. That's not John's case, so I leave it blank and I also paint it yellow, manually.

Jane worked all the 5 days, without missing any of them. So no yellow background, all 5 days counted on column G, all smooth and perfect. Jane is going to have the "reward", so I manually place the value "Y" there at column H (that value is always the same for all the members).

Sam worked only 2 days, but he warned us that he was going to miss the other 3 days, so I didn't painted all that cells in yellow (because yellow means exclusively days missed without warning). But also, for him to receive the "reward", he needed to work at least 4 days. That's not the case, so I didn't add the "Y" value to that cell, and also I didn't painted it yellow.

What I need (short version):

I need some formulas for the following cases (rows, columns and cells are based on the picture):

1. IF there's at least 1 yellow cell painted between B2 and F2, THEN paint A2 AND H2 in yellow too
2. IF the G3 is greater than 12 AND H3/A3 (it's the same) is NOT yellow, then add "200" at H3 (same formula repeats all the way through column H)

I think that'll be all. If I think of anything else you have to consider for the formulas, I'll let you know. Also please don't hesitate in asking me anything that may help you for finding the solution.

Thanks in advance!!!

 

[Joe4]

Welcome to the Board!

Excel formulas can only reference the value in cells, not the formatting of cells (i.e. cell or font colors). That would require VBA. However, if the cells are colored for some reasons that can be represented by formulas (i.e. they care colored because they are blank), they you can test for those conditions instead of trying to test for the color.

In order to change the formatting of one cell based on the values in cells, you can use Conditional Formatting.

 

[Joyner]

To continue what Joe4 has said, in your situation since you have two cases where the cell is blank, what I’ll call an excused absence and an unexcused absence, you won’t be able to just key off of a blank cell. If it were me, I would modify the process slightly to make things easier. Instead of manually coloring the cells, I would use a code (value) in the cell and let the Conditional Formatting do the coloring.

For example, you enter a “X” for the employee being present, you could use an “E” for excused, and a “U” for unexcused (or anything you want), then in cells B2 to F4 use conditional formatting, Highlight Cells Rules, where cell is equal to “U” and color yellow. Then for columns A and H you can use this formula in conditional formatting to color them yellow:

=COUNTIFS($B2:$F2,"U") (put this is A1 and H1 and copy the format down)

For part 2: “IF the G3 is greater than 12 AND H3/A3 (it's the same) is NOT yellow, then add "200" at H3 (same formula repeats all the way through column H)”: I wasn’t sure what you were asking since the cell references and the data don’t seem to match you example above.

 

[nchiari]

Instead of manually coloring the cells, I would use a code (value) in the cell and let the Conditional Formatting do the coloring.

The thing is that when the person is present, I have to type in the cell the cash he made that day (which is always different). So I just cannot place a reference value for the conditional formatting. What I can do is in the cells that I don't have anything to put on, those are the days he missed work (excused or unexuced). So I can actually use the conditional formatting to paint the unexcused absence cell yellow and also I take advantage of giving a value to that cells.

For part 2: “IF the G3 is greater than 12 AND H3/A3 (it's the same) is NOT yellow, then add "200" at H3 (same formula repeats all the way through column H)”: I wasn’t sure what you were asking since the cell references and the data don’t seem to match you example above.

Yes sorry, in the example I used the number 5 in G3, but in reality I need te formula to be greater than 12, not 5 as the example. I'm sorry if it wasn't clear.

 

[nchiari]

So let's say that I make a conditional formatting that says that if a cell contains the letter Z, paint that cell in yellow. In that case, I can actually make a formula that says that in any given day there's a cell with the value Z (and Z only, not "that contains Z"), then give the value Z to another cell (that means, paint that other cell yellow too). BUT ALSO, If there's no day with Z value, AND I have any value other than Z (no matter the value, they're numbers), with a minnimun of 12 cells with a value typed on it, I place the number "200". AND ALSO, if there's no Z value on any day, but there's neither at least 12 cells with a value, just leave that other cell blank.

Oh men, I think I'm starting to get it. I think that it's more difficult to explain that to make it hahaha.

 

[nchiari]

I'm getting it. What I need to know that is how do I tell Excel, for instance, that "IF from cells A2 to F2 are any cell with the exact value Z (not "that containts Z"), then assign the value Z to this cell too"

 

[Joyner]

I'm not exactly sure what you want so another sample layout with expected results would help. But if you mean you want to return a "Z" in a cell if there is a "Z" in a range like B2 to F2, then you can use a formula like:

=IF(COUNTIFS($B2:$F2,"Z"),"Z","")

If you are using that formula to just color a cell, you can use conditional formatting with just:

=COUNTIFS($B2:$F2,"Z")

 

[nchiari]

Don't worry, I got it!!!! But I have a different problem now. Let me type you an example of the formula I got:

CELL: AI2
=IF(COUNTIF(C2:AE2;"Z")>0;"Z";IF(AF2>=12;"$200";"")) (Z means "paint it yellow")

What I need now, is that another SUM formula takes that "$200" as a number value to add it to a total SUM from another cells. That's not working right now:

CELL: AL2
=SUM(AG2:AK2) (this formula is not taking in count the when AI2 = $200, it doesn't add that value to the total number)

 

[nchiari]

I GOT IT!! It was my mistake. I have to put only 200 (without quotes) and not "$200".

Guys, you actually helped me a lot! It may seem a bit confusing how, but trust me, without your input I wouldn't be able to make this work!

THANK YOU SO MUCH!!!

 

[Joyner]

You are welcome, glad you figured it out - a common mistake putting numbers in quotes so it is Text not a number.