Conditional Format Occurrences of Values In a Table

[BWMagee]

Hi,

In Sheet1, A1, I enter a value. Eg. 3. This corresponds to a value in "Person ID"
In Sheet1, A3:10, I have numeric values 1, 2, 3, etc… This is “Group ID”

In Sheet2, A3:10, I have numeric values 1, 2, 3, etc… This is “Person ID”
In Sheet2, B3:F10, I have various numeric values. These correspond to “Group ID”

What I would like to do, using a formula in conditional formatting, is colour the values in Sheet1, A3:10, that are found in one row of the B3:F10 table. The row in which we look is determined by the value placed in Sheet1, A1.

Eg. If we enter "3" in Sheet1, A1, this means we look in row B3:F3. If the values 4, 6 and 8 are found in that row, and they are also found in Sheet1, A3:10, then we colour them using conditional formatting in Sheet1, A3:10. If Sheet1, A3:10 also contains other values that are not found in B3:F3 (eg, 5, 7 and 9), then these are not coloured.

I know how to do this when looking in a single row, but not within a table. For a single row, the formula applied to Sheet1, A3:10 in conditional formatting, would be:

=IF(COUNTIF('Sheet2'$B$3:$F$3,'Sheet1'A3),1,0)

The problem is, I will not always be looking in B3:F3, sometimes I will look in B4:F4, or B5:F5, etc… depending which value I enter into Sheet1, A1. How can I write a formula that does that?
Many thanks!

 

[BWMagee]

However, you are then expecting helpers to manually type out that sample data to test with.
You will generally get many more potential helpers and faster replies if you make it easier for helpers by providing sample data in a format that can quickly copy from the forum into their own test file. That is, XL2BB

Sorry, last time I logged into these forums there was no XL2BB, so it was never a problem for anyone. I'd never heard of it and don't know how it works.

But your solution worked perfectly! Thank you both very much for your help.

 

[Peter_SSs]

But your solution worked perfectly! Thank you both very much for your help.

You're welcome. Glad it worked for you.

Sorry, last time I logged into these forums there was no XL2BB, so it was never a problem for anyone.

I disagree, I have been helping here for over 15 years and it has always been a problem if helpers have to manually type sample data to test with.
That is why before XL2BB we had similar tools called 'MrExcel HTML Maker' and 'Excel jeanie', allowing easy copying of data, formulas etc.

I'd never heard of it and don't know how it works.

That is why I provided you with a hyperlink to the installation and use instructions.


Remember also, this is still relevant. Different Excel versions have different functions and features available to them.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)