0

I have a dropdown box in column A. Lets say I'm working row 10. I select "Highlight" in cell A10. When I do that, I would like a conditional format that highlights any cells in column B that match cell B10. How can I do that? Any help would be greatly appreciated.

8
  • 1
    Is this along the lines of what you're trying to do? exceljet.net/formula/highlight-cells-that-equal
    – user2800
    Oct 12, 2018 at 15:51
  • @user2800 - It looks like it, except OP has a problem: There will be a circular reference. If you're trying to look in column B for cells that contain the text that a specific cell in that column has, I can't think of a way to write a formula without it being a circular reference.
    – BruceWayne
    Oct 12, 2018 at 16:03
  • 1
    @BruceWayne I agree. One option may be to add a helper column C that checks the adjacent cell in Column B for a match to B10. Something like =IF($C2=$B$10, TRUE, FALSE), and dragging down. Then the conditional formatting becomes fairly straightforward.
    – user2800
    Oct 12, 2018 at 17:13
  • @user2800- Great idea, a helper column would make this much simpler and avoid VBA.
    – BruceWayne
    Oct 12, 2018 at 17:15
  • So maybe I'm missing something, but conditional formating with the following formula: =AND(B1=B$10,$A$10="Highlight") applied to column B worked fine for me. Problem is, you need a condition for every row in column A that has a dropdown, which may be a lot... Oct 12, 2018 at 21:41

2 Answers 2

0

enter image description here

How it works:

  • Select entire B Column.
  • Apply this Formula as New Rule & an appropriate Color to the column B:

    =AND($B1=$B$10,$A$10="Highlight")
    
6
  • Hi Rajesh, thanks for the suggestion. I'm just wondering what is the significance of B1? How does it figure into the formula? Do I need to create a reference cell?
    – bherro
    Oct 16, 2018 at 12:11
  • You can find the Numbers in Column B to highlight so as abandon precaution better start with First Cell B1 and you need to change it according to the Column $C1 for Column C. ☺ Oct 16, 2018 at 12:18
  • @BrianH, significance of $B1 is very simple the Conditional format Formula will check Entire column B starts from Cell B1. if you put 100 in B1 will be Highlighted !! Oct 17, 2018 at 12:42
  • I really appreciate your help. Unfortunately it didn't work. But with the help of a number of helper columns I was able to figure it out. But thanks again for all your help. I think the problem with this solution might relate to the requirement that there be a highlight. I think this prevents the formula from finding the duplicates that don't have a highlight in their respective column A cells.
    – bherro
    Oct 17, 2018 at 13:11
  • @BrainH,,, I'm surprised,,, the attached screen shot simply shown that conditional format highlights numbers in column B if are 100 & A10 is Highlight. Could you edit your Post & include the Screen Shot !! Oct 17, 2018 at 13:16
0

With the helper columns idea, I figured it out. I probably didn't need all the helper columns, but they made it easier. Column C returns a "TRUE" value if a cell in column B has a match in the same column. Column D lists all duplicate values in column B. Column E identifies a value that has duplicates only if there is also a "Highlight" in A. Column F searches column D (duplicate entries) and returns only those that match column E Entries. The conditional format scans column F for nonblank cells, and highlights their counterparts in column B.

Column C: =COUNTIF($B$1:$B$10,$B1)>1

Column D: =IF($C1=TRUE,$B1,"")

Column E: =IF(AND($A1="Highlight",$C1=TRUE),$D1,"")

Column F: =IF(ISERROR(MATCH($D1,$E$1:$E$10,0)),"",$D1)

Conditional format applied to $B$1:$B$10 is: =$G1<>""

And that highlights any values in column B that match a highlighted row's cell B.

Thanks, everyone, for your ideas! You helped me figure this out!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .