Solved

need help writing a complex cell condition formula

Posted on 2015-01-09
13
94 Views
Last Modified: 2015-01-23
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
0
Comment
Question by:the_hero
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40541352
Do you accept a VBA solution ?
gowflow
0
 
LVL 1

Author Comment

by:the_hero
ID: 40541397
If you can provide instructions for implementing.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40541400
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40541434
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
 
LVL 1

Author Comment

by:the_hero
ID: 40541443
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
 
LVL 1

Author Comment

by:the_hero
ID: 40541462
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:gowflow
ID: 40541748

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
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 100 total points
ID: 40541903
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 200 total points
ID: 40541904
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
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 200 total points
ID: 40543267
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
 
LVL 1

Accepted Solution

by:
the_hero earned 0 total points
ID: 40557038
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40558814
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
 
LVL 1

Author Closing Comment

by:the_hero
ID: 40566018
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now