Conditional Formatting Help

[Toliandar]

Hi there,

I'm fairly new to Excel but I've been putting together a work spreadsheet. It's been requested that when certain offence codes appear that their cell highlights (or better yet the whole row!). I know I could get input a bunch of individual conditional formatting rules but there has to be a better way.

The codes would appear in column E

The possible data/codes are "1940","1960","1941","1961","1942","1962","1938","1958","1939","1959","1929","1949","1930","1950","1931","1951","1932","1952","1933","1953","1934","1954","1991","1992","1994","1996","2093","8321","2095","2096","2097","2098","2099","7389","7390","1999","3220","5851","5852","5853","5854","5855","5856","5857","5858","5859","5860"

Basically I would like it either so the specific cell in column E highlights or the row (at least up to column E). I've looked for guides but haven't found anything that worked and most of them deal with simple conditional formatting where a simple pattern/trend exists. Technically I suppose you could say these possible values represent multiple trends and if necessary I'll enter in multiple formatting rules that relate to the possible trends.

[xlnitwit]

Hi,

I suggest you put the codes into a range, then use a formula rule like
=MATCH($E2,range_with_codes,0)
applied to the entire range you want coloured. I've assumed that starts in row 2 but if not, adjust the 2 in the formula to match the first row of the data.

[Glenn Kennedy]

Try:

=ISNUMBER(MATCH($E2,$Q$2:$Q$47,0))

[kev_]

another option

cf formula
=COUNTIF(Sheet1!$A:$A,$E2)>0

The value of each cell in column E (sheet2) is checked against the list of codes in column A of sheet1.

see attached workbook

cf_56.jpg

Formula is in column H so that you can see why it works
- column H is not part of the solution