Forum Discussion
Excel Help for Conditional Formatting
Hi,
can someone help me with a relatively simple conditional formatting?
I have one table with approx 100 numerical values that I need to confront with another table of 60 numerical values.
What I'm tring to do is conditional formatting of the cells of the first table based on the values of the second table.
For example: let's assume that first element in first table is 2, I would like this cell to become red if one (no matter in which position) of the cells in second table is 2.
As a bonus (but not critical), it would be great if I could change the colour of the cell in first table if there are more than one matching value in second table.
Select the first range. The top left cell of that range should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=COUNTIF(second_range, top_left_cell)>0
The reference to the second range should be absolute, but the reference to the top_left_cell should be relative.
Click Format...
Activate the Fill tab.Select red as highlight color.
Click OK, then click OK again.Optional:
Repeat these steps, but with
=COUNTIF(second_range, top_left_cell)>1
and another color.
