Forum Discussion
Conditional formatting to highlight a cell if a certain value appears in another sheet
I am needing to add shading to a cell to highlight if a figure appears in another tab:
For example, highlight column B, next to 1.1.1, if 1.1.1 appears in any of the cells in columns B-H on the 'Sample' sheet:
I will then replicate this for multiple sheets, and multiple columns on the 'Coverage tab', can anyone help please?
Sheet names that contain spaces or punctuation must be enclosed in single quotes:
=COUNTIF('Paper 1'!$B:$H, A3)You can also do the following:
In B3:
=COUNTIF(INDIRECT('"&B$2&"'!$B:$H"), $A3)This can be filled or copied down and to the right.
Select B3:B27. B3 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(Sample!$B:$H, A3)
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.Thank you so much, how would I replicate for other sheets? eg the same thing for for multiple tabs and multiple columns?
I tried copying the formula and replacing 'Sample' with 'Paper 1' but it came up with an error message so assuming I did something wrong!
Sheet names that contain spaces or punctuation must be enclosed in single quotes:
=COUNTIF('Paper 1'!$B:$H, A3)You can also do the following:
In B3:
=COUNTIF(INDIRECT('"&B$2&"'!$B:$H"), $A3)This can be filled or copied down and to the right.
