Conditional Format cells with values that match the value of a cell within a range

[Denny57]

I am looking to apply conditonal formatting to those cells in the range D6:I50 whose values match the value of any cell in the range D3:I3.

I found this example in a previous enquiry on this forum which was trying achieve a similar result for the Range A1:A20 when matching against values in the range B1:B10.

Highlight Range A1:A20 then in CF under Formula type the following
=MATCH(A1, $B1:$B10, 0)

I tried to adapt this to =MATCH(D6, $D$3:$I$3,0) but to no avail.

Is what I am trying to achieve possible and if so can someone provide the appropriate formula.

 

[swapnilk]

To apply conditional formatting to a range of cells in Excel such that the cells are highlighted if their values match the value of any cell in another range, you can use the following formula:

=COUNTIF($D3:$I3, D6:I50)

This formula uses the COUNTIF function to check each cell in the range D6:I50 to see if its value exists in the range D3:I3. If the value exists, the COUNTIF function will return a value greater than 0, which will trigger the conditional formatting rule and highlight the cell.

To apply this formula as a conditional formatting rule, select the range D6:I50, then go to the "Home" tab and click on the "Conditional Formatting" button. From the dropdown menu, select "New Rule". In the "New Formatting Rule" dialog box that appears, select "Use a formula to determine which cells to format" and enter the formula =COUNTIF($D3:$I3, D6:I50) in the "Format values where this formula is true" field. Finally, choose the formatting you want to apply to the cells (e.g. a fill color or font color) and click "OK" to save the rule.

Note: The dollar signs ($) in the formula are used to create an absolute reference to the range D3:I3. This means that the range will not change when the formula is copied to other cells. If you do not want to use an absolute reference, you can simply use the formula =COUNTIF(D3:I3, D6:I50) without the dollar signs.

 

[Fluff]

In what way didn't this work

=MATCH(D6, $D$3:$I$3,0)

 

[Denny57]

To apply conditional formatting to a range of cells in Excel such that the cells are highlighted if their values match the value of any cell in another range, you can use the following formula:

=COUNTIF($D3:$I3, D6:I50)

This formula uses the COUNTIF function to check each cell in the range D6:I50 to see if its value exists in the range D3:I3. If the value exists, the COUNTIF function will return a value greater than 0, which will trigger the conditional formatting rule and highlight the cell.

To apply this formula as a conditional formatting rule, select the range D6:I50, then go to the "Home" tab and click on the "Conditional Formatting" button. From the dropdown menu, select "New Rule". In the "New Formatting Rule" dialog box that appears, select "Use a formula to determine which cells to format" and enter the formula =COUNTIF($D3:$I3, D6:I50) in the "Format values where this formula is true" field. Finally, choose the formatting you want to apply to the cells (e.g. a fill color or font color) and click "OK" to save the rule.

Note: The dollar signs ($) in the formula are used to create an absolute reference to the range D3:I3. This means that the range will not change when the formula is copied to other cells. If you do not want to use an absolute reference, you can simply use the formula =COUNTIF(D3:I3, D6:I50) without the dollar signs.

I have tried to apply this but I keep getting totally random results

I have tested by giving Cells D3:I3 the values 1 through 6 (D3=1, E3=2 etc) and entered various cells in the range D6:I50 with these values to no effect. Instead,
I get the formatting in random cells in range D6:I50 where the values are not 1 - 6. (eg 23,11,54).
Additionally, some cells containing these values are not formatted..

So I created a test file with a small sample with the range D3:I3 given values I through 6 and a Smaller Grid (D7:I14) in which only one cell formatted (G11), but this has a value of 34. If I change the value of this cell the formatting disappears until I return it to 34. Other cells in this range have a value of 1 thru 6 but are not formatted. If I change the value of different cells to a value of 1 thru 6, these also do not get formatted.

 

[Denny57]

In what way didn't this work

=MATCH(D6, $D$3:$I$3,0)

Basically the cell does not format, beyond that I Cannot see any reason why this should be the case

 

[Fluff]

What is the applies to range for the rule?

 

[Denny57]

Basically the cell does not format, beyond that I Cannot see any reason why this should be the case

I have created another test file and this time the formatting has worked...Confused

 

[Fluff]

Which formula was that using?
Also can you answer my question in post#6?