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.
2 Answers
How it works:
- Select
entire B Column
. Apply this Formula as
New Rule
& an appropriateColor
to the columnB
:=AND($B1=$B$10,$A$10="Highlight")
-
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?– bherroOct 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 theConditional format Formula
will checkEntire column B
starts from CellB1
. if you put100
inB1
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.– bherroOct 17, 2018 at 13:11
-
@BrainH,,, I'm surprised,,, the attached screen shot simply shown that conditional format highlights numbers in column
B
if are100
&A10
isHighlight
. Could you edit your Post & include the Screen Shot !! Oct 17, 2018 at 13:16
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!
=IF($C2=$B$10, TRUE, FALSE)
, and dragging down. Then the conditional formatting becomes fairly straightforward.=AND(B1=B$10,$A$10="Highlight")
applied to columnB
worked fine for me. Problem is, you need a condition for every row in columnA
that has a dropdown, which may be a lot...