-2

I want to know how I can use an IF statement (or another function) to determine if all the columns pictured below in a particular row are highlighted:

Screenshot of Table

I'm looking to put the function in Column T and return "Buy" if all columns within the row are highlighted.

Thanks in advance

mpharvey
  • 3
  • 3

1 Answers1

0

Now I have tested it but not sure what green you use. Change it if I'm incorrrect.

Paste this in a vba module:

Function color(c As Range)
       If c.Interior.color = 5296274 Then color = True
End Function

Then use it like:

=color(A1)

It will return true if A1 is green.
In your case you need to build a and like: 

=and(color(A1), color(B1))

And so on...

And you need to save the workbook as macroaktivated workbook xlsm.

Edit added picture and other colorcode. This is the left green color of "standard colors".

enter image description here

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • I apologize for not giving more information. I tried your method but it is not working for me. I developed a table that uses conditional formatting to determine if the values in the column are > or < the median value of the column. So I want to count how many of the column values for each row are highlighted. I've done all the highlighting in green font/green fill. I found this post on stack that references the RGB scale for this: http://stackoverflow.com/questions/27611260/what-is-the-rgb-code-for-the-conditional-formatting-styles-in-excel Those values didn't work either; thoughts? – mpharvey Sep 17 '16 at 20:10
  • Nope not really. If you set a breakpoint on the "if" line, what is the color code on your cell? (hover the mouse on color of c.Interior.color and the value should appear in a box) – Andreas Sep 18 '16 at 09:30
  • This is the color # 16777215. Using that, your method worked, so thank you. However, I added additional columns so none of the rows have every column highlighted. So I'd liked to do a countif for the highlighted cells, but it doesn't seem there's a straightforward solution for that either. Any suggestions? – mpharvey Sep 19 '16 at 21:19
  • I think you can edit the code in the vba to `.... Then color = 1` and it will return 1 for each green. That way you could probably do `=if(color(A1)+color(B1)+color(C1)=3, "all green", "not all green")` it will be a "pain" to maintain it if you add stuff. Another method is to use a different sheet to count. So on sheet2. `=color(Sheet!1A1)` then you just sum(), and link back the value to sheet1. That way you can easily add columns and all calculations and formulas can be "filled right" and your Sheet1 will be clean – Andreas Sep 20 '16 at 05:44
  • Ok, that's a good idea. I will give it a shot. Thanks for your help. – mpharvey Sep 25 '16 at 18:58