• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Excel macro or formula

Hi,

I have a excel that has content in column "D" that needs to be checked in column "C" if available color to yellow

Note : Column "C" has duplicates and that should not be checked.

thanks
0
bsharath
Asked:
bsharath
  • 6
  • 5
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

your question is not clear. Please post a sample file and manually put in the desired results.

cheers, teylyn
0
 
McOzCommented:
As I understand it, you want to color cells in column D yellow if the value exists in column C.

You can do this with conditional formatting, using the following custom formula applied to the cells in column D:

=COUNTIF($C:$C, D1)>0

McOz
0
 
bsharathAuthor Commented:
Column D has example 100 cells content and unique but column "C" has duplicates

so column "C" has to be colored
Duplicates within column should not be colored but if duplicate in column "C" and "D" then color it

example

Colum "C"
Godaddy
Godaddy
Godaddy
Storm
Experts
website
website
Journal
journal

Colum "D"
Godaddy
Experts
website

Colum "C" Godaddy all 3 has to be colored
Experts and website should be colored
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
McOzCommented:
OK, change the conditional formatting formula to this:

=COUNTIF($D:$D, C1)>0

and apply the conditional formatting to cells in column C.

Should do the trick.
0
 
bsharathAuthor Commented:
Sorry not sure how to do conditional formatting

i pasted in E and get true and false which seems wrong

Any way to paste in "E" and get this done
0
 
McOzCommented:
It depends a little on your version of excel, but basically you select the cells that you want to have the color change (in your case, all cells in column C).

Then, if you are in Excel 2007 or above, go to the "Home" tab, click "Conditional Formatting" > "New Rule"

Click "Use a formula to determine which cells to format"

Paste the formula above into the field provided for the formula.

In the same box, click the "Format" button to specify how you want the cells to appear (in your case, give it a yellow fill)

Click OK

Here is a link that shows how to do it: http://www.techrepublic.com/blog/msoffice/create-your-own-conditional-formatting-rules-in-excel-2007/814
0
 
bsharathAuthor Commented:
Thanks
Works fine
In column "D" also can we color cells that found a match and if possible in column "E" if it can show how many times this word was found in Column "C"

thanks a lot
0
 
McOzCommented:
Sure. In column D add another conditional formatting with the custom formula:

=COUNTIF($C:$C, D1)>0

And in Column E add the following formula to the cells themselves (not conditional formatting this time):

=COUNTIF($C:$C, D1)

Cheers
0
 
bsharathAuthor Commented:
I used this
=COUNTIF($C:$C, D1)>0

i got all cells in the colum "C" with color yellow


this the counts show wrong
=COUNTIF($C:$C, D1)

One name is 3 but shows as 13 times
0
 
McOzCommented:
It may have to do with the cell references. Make sure you paste the count formula in E1 and then drag it down to the rest of the column, so that the formula refers to the cell immediately on its left.

So the formula in E1 would be =COUNTIF($C:$C, D1)

E2 would be =COUNTIF($C:$C, D2)

etc.

If it still doesn't make sense, double-check your data - do you have any rows hidden or filtered, etc.

Good luck
0
 
bsharathAuthor Commented:
Is there any way i can get this done with a formula.

I am not sure if my excel has some issue etc

Attached is a sample file

i want Column "C" to be colored just users who are in column "D"

then colum "E" shows how many times each name in column "D" is available in Column "C"
sample.xlsx
0
 
McOzCommented:
OK, here is your spreadsheet. It works like you want on my machine (Excel 2010).

You had the correct formula in Column E, it is showing the correct count as far as I can tell. I just cleaned up your conditional formatting rules.

McOz
sample.xlsx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now