Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# need help writing a complex cell condition formula

Posted on 2015-01-09
Medium Priority
106 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
Question by:Shaye Larsen
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 4
• 2
• +1
13 Comments

LVL 31

Expert Comment

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

LVL 1

Author Comment

ID: 40541397
If you can provide instructions for implementing.
0

LVL 31

Expert Comment

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 23

Expert Comment

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)
``````
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

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

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

LVL 31

Expert Comment

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 23

Assisted Solution

Ejgil Hedegaard earned 300 total points
ID: 40541903
0

LVL 50

Assisted Solution

barry houdini earned 600 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 31

Assisted Solution

gowflow earned 600 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

Shaye Larsen earned 0 total points
ID: 40557038
0

LVL 50

Expert Comment

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month11 days, 4 hours left to enroll

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

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