need help writing a complex cell condition formula

See the attached doc.  I need to format the sheet to do the following.

If a cell in column F says "Online", then highlight the email a certain color.
If an email is highlighted in that certain color, highlight any other email that is the same, the same color.
test.xlsx
LVL 1
Shaye LarsenAsked:
Who is Participating?
 
gowflowCommented:
Do you accept a VBA solution ?
gowflow
0
 
Shaye LarsenAuthor Commented:
If you can provide instructions for implementing.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gowflowCommented:
Here it is thru vba but it is not perfect !!!

color any email and press enter then select again this email and it will color all of the same emails with that color. now remove the color and it will remove for the rest as well.

If you accept a VBA then I will try to see how to get the macro to kick on color change as I know there is no event that kicks you need to either change the cell or move back and forth to it.

gowlfow
test.xlsm
0
 
Ejgil HedegaardCommented:
Here is a solution with conditional formatting.
It use a helper column K, setting a sequential number from 1 up, for each different mail where column F has Online at least once.
The formula for K2 is
=IF(COUNTIFS($C$2:$C$100,C2,$F$2:$F$100,"Online")>0,IF(COUNTIF($C$1:C1,C2)=0,MAX($K$1:K1)+1,INDEX($K$1:K1,MATCH(C2,$C$1:C1,0),1)),0)

Open in new window

Then the calculated number is used to set the conditional format colour.
The conditional formula looks for the number in column K.
The attached sheet is made up to row 100.
Insert rows if you want more, then the formulas will adjust.
Then copy the formula to the inserted rows.
I have made it to handle 5 conditional colours.
If more is needed, extra conditions must be made.
Colour-identical-emails.xlsx
0
 
Shaye LarsenAuthor Commented:
I'm not sure how to follow your instructions.

I tried coloring (font color?) an email (the text in the cell) and then hit enter, then clicked on the same and nothing happens.

Again, I can accept VBA if you can provide instructions of how to implement.  I've never done a VBA.

Thanks
0
 
Shaye LarsenAuthor Commented:
Ejgil Hedegaard, I loaded your sheet.  Thank but it doesn't meet my requirement.  I basically need any row that has "Online" in the "F" column to highlight the email in that row BLUE, and if that same email is on another row, highlight it the same blue.
0
 
gowflowCommented:

I tried coloring (font color?) an email (the text in the cell) and then hit enter, then clicked on the same and nothing happens.

 Again, I can accept VBA if you can provide instructions of how to implement.  I've never done a VBA.

1) Load the file I posted
2) Once loaded you will have abutton saying Options next to the Address bar on top of the columns letters
3) Press on it and Say Enable
4) Macro are now activated. click on a cell that have an email and color it then press enter the cursor will move to the cell below it and will color the previous cell in the color chosen.
5) press on the arrow up to move back to the previous cell and all the same emails will be colored to the same color.

Is that what you want ?
You can try removing the color from that email it will remove it also from all the similar emails.

If this is what you want and all is ok like this then I can give you steps to implemented in your production workbook.

gowflow
0
 
Ejgil HedegaardConnect With a Mentor Commented:
0
 
barry houdiniConnect With a Mentor Commented:
I basically need any row that has "Online" in the "F" column to highlight the email in that row BLUE, and if that same email is on another row, highlight it the same blue.

You can do that with a relatively simple formula in conditional formatting

I selected column C and then I used this formula in conditional formatting

=COUNTIFS(C:C,C1,F:F,"Online")

I then applied blue fill format

See attached

regards, barry
CF-test-barry.xlsx
0
 
gowflowConnect With a Mentor Commented:
Here is the solution that fixes both the background and the font, as I just realized that you had tried the change the font and nothing happened obviously becoz the solution was applied only on the background.

This solution is much simpler for you as you do the following no back and forth with re-selecting the cell as found a flop in this approach especially when similar cells follows if you change the first one it will not trigger a change color.

now you choose the email that you want, color the background or the font and then once done simply DOUBLECLIKC on that cell and it will change all similar emails to whatever you colored.

PS @Barry, problem is that asker clearly mentioned 'certain color' and not a specific color which means that any emaily could be colored differently therefore setting it to only blue won't help unless we mis-understood the post !

gowflow
test-V01.xlsm
0
 
barry houdiniCommented:
Hello the hero,

It would have been good if you could have responded to the suggestions provided - 3 experts have put forward 3 different solutions but you haven't given any feedback - giving constructive feedback would have allowed one or all of us (or another expert) to modify the "inadequate answers" provided, and possibly imbue them with a modicum of adequacy.

regards, barry
0
 
Shaye LarsenAuthor Commented:
Inadequate answers provided, got is solved on a different thread.  Posted the link. http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28594076.html#a40553854
0
All Courses

From novice to tech pro — start learning today.