conditional formatting question thanks for the help

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
Hi, I dont know if this is difficult or easy but I will try to ask it anyways.
I have a list in column A of items and the costs next to them and they change monthly. In column C I have a list of the ones which appear mostly often and a sumif with the costs next to it which show all of the costs of the specific items.
My question is, is there a formula or macro or something to highlight the cells In column A if they are costs associated with them in C.

So I know it is a little confusing but here it is visually.
ItemCostItemCost
Dog20Dog85
Cat10Cat15
Rabbit5Rat
Dog10
Cat5
Dog5
Rabbit300
Monkey400
Parrot400
Dog50

<tbody>
</tbody>

So as you can see the Dog and Cat are included in the lis on the left so what the highlighting will do is because the dog and the cat on the right are shown added up it will highlight all of the dogs and cats on the left. There are no rats so those will not be highlighted neither the monkey or parrot etc...

Thanks a bunch!! Appreciate the help!!!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Conditional formatting should do the trick for the cells in column A:

Code:
=not(iserror(vlookup($a2,$c$2:$c$4,1,false))
 
Upvote 0
The formula would go in the conditional format for cell a2.

Click on A2, then on the Home tab click Conditional formatting, it is in the Styles Group, select New Rule.

Select "Use a Formula to determine which cells to format" then paste in the formula and select the desired formatting.

Finally if the result works to your liking, copy cell a2, select the range in column A you want to apply the format to, and click paste special - formats.
 
Upvote 0
Thanks Gerry,
I appreciate the help. I appologize for the late reply. i finally finished travleling to take a look at it. :)
OK I tried it and it said there were to few arguments...hmmm.. I am trying to figure out why that is. Sorry, my level of excel is beginner. Thanks!!!
 
Upvote 0
The formula appears to have lost a bracket at the end, try this:

Code:
=not(iserror(vlookup($a2,$c$2:$c$4,1,false))[COLOR=#ff0000])[/COLOR]</pre>
 
Upvote 0
Hi Gerry,
Thanks a lot for the help! I really do appreciate the help more than you think and It really will help what I am doing at work :)... Therefore if you cant help me more I appreciate it and thank you so far for what you have done.

At the bottom of this reply is the origional problem.
I did take a look at the problem and with the other bracket it did work (HOW EMBARASSING FOR ME) :)

The only question i have is: a cell in the F column gets highlighted.

I am curious to see if we can highlight the cells in column a which repeat.

For example you can see that there are 85 representations of dog and 15 of cat. So if it picks up that these things are representative in colum A, it will highlight all results of dogs and all results of cats

So Cells A2, A5, A7, A11 will be highlighted as there are uses of dog. And I guess I could do another formula to have the ones with cats highlighted.
Again I praise your time that is why I am humbled by your help you are able to give. thank you!

ItemCostItemCost
Dog20Dog85
Cat10Cat15
Rabbit5Rat
Dog10
Cat5
Dog5
Rabbit300
Monkey400
Parrot400
Dog50

<tbody>
</tbody>
 
Upvote 0
Thanks for letting me know it worked! It is nice to have the feedback.

I don't think i am following your last question... do you want the items in Column A to be colored the same as their corresponding item in Column C?
(You say F column, but I do not see that column)

If so then try these three formulas, all applied to column A and C, notice that the A2 is relative, so apply the format to A2 then copy A2's formats to all the other cells you want it applied to.
Code:
First Condidtion
     =1=iferror(match(a2,$c$2:$c:$4,0),0)

Second Condidtion
     =2=iferror(match(a2,$c$2:$c:$4,0),0)

Third Condidtion (I'm sure you see the pattern now)
     =3=iferror(match(a2,$c$2:$c:$4,0),0)


If i've misunderstood, i'm glad to provide another formula, or even have a look at your sheet if you have dropbox or google drive, just shoot me a PM.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top