Conditional formatting one cell to a column

[Billvh]

I have a workbook with several worksheets in it.

I want to conditional format one column on one work sheet when:

• It matches the same exact number that could be in and any cell in a column in another worksheet
• Example: conditional format if – Review $G$11:$G$107 matches List $C$5:$C$329

Could anybody help me with this?


Thanks,

 

[Billvh]

I have a workbook with several worksheets in it.

I want to conditional format one column on one work sheet when:

• It matches the same exact number that could be in and any cell in a column in another worksheet
• Example: conditional format if – Review $G$11:$G$107 matches List $C$5:$C$329

Could anybody help me with this?


Thanks,

I seen that there were twenty views and no replies. Is this something that can't be done?

Thanks,

 

[Marcelo Branco]

Select G11:G107

In CF pick the formula option and insert this formula
=ISNUMBER(MATCH(G11,List!$C$5:$C$329,0))

Format button and pick the format you want

M.

 

[DanteAmor]

Or a variant of Marcelo:

Select the cells in your List sheet and create a range name: "ListValues"

Select G11:G107
In CF pick the formula option and insert this formula

=VLOOKUP(G11,ListValues,1,0)>0

 

[Eric W]

Just as a point of interest, the ISNUMBER() is not required for a CF formula, since an error equates to FALSE.

 

[Marcelo Branco]

Or a variant of Marcelo:

Select the cells in your List sheet and create a range name: "ListValues"

Select G11:G107
In CF pick the formula option and insert this formula

=VLOOKUP(G11,ListValues,1,0)>0

Hi Dante

I think your formula doesn't work with negative numbers.
OP said:
"It matches the same exact number that could be in and any cell in a column in another worksheet"

M.

 

[Marcelo Branco]

Just as a point of interest, the ISNUMBER() is not required for a CF formula, since an error equates to FALSE.

Eric,

Good point

M.

 

[DanteAmor]

Hi Dante

I think your formula doesn't work with negative numbers.
OP said:
"It matches the same exact number that could be in and any cell in a column in another worksheet"

M.

You're right, with negatives does not work, how about

=VLOOKUP(G11,ListValues,1,0)<>""

 

[Billvh]

This was great help, thank you!

I have one more question if you don't mind.

I'm comparing a cell in column G and seeing if it matches any of the cells in column C on the List worksheet.
With the above formula it will conditional format the cell in column G that matches on of the cells in column C on the List worksheet.

This is great and I can use it, but what I really would like is for this to happen:
---when the cell in column G matches on of the cells on the List worksheet in column C, I would like to conditional format a blank cell in column S on the original worksheet.

Is this something that can be done?

 

[DanteAmor]

Add the $ symbol to the formula:

=VLOOKUP($G11,ListValues,1,0)<>""

In Applies to:

=$S$11:$S$107