Highlight multiple cells based on cell value

[mrbret]

Hello all.

I need some help again.

I need all cells in column A4 to A200 with multiple names (bob, steve, sue, Karen, etc) to highlight yellow with anything not listed to remain as default.

I know this is not the formula but something like:
=if(a2:A200="bob, steve, sue, Karen),turn name/cell yellow

Thanks for any help

[DBY]

Hello
If you mean there are single names in each cell in A2:A200, then something like the following Conditional Formatting formula should work:

Please Login or Register  to view this content.

Applied to A2:A200, 'Names' being the range with your criteria.

DBY

[mrbret]

Hello
If you mean there are single names in each cell in A2:A200, then something like the following Conditional Formatting formula should work:

Please Login or Register  to view this content.

Applied to A2:A200, 'Names' being the range with your criteria.

DBY

Wont that just count the names?

[FDibbins]

multiple names (bob, steve, sue, Karen, etc) to highlight yellow

Do you mean you want to count want to shade all cells with any of those names, or just cells that contain more that 1 instance of "bob" (for instance?

If you just want to highlight every time "bob" (or any name) gets repeated, look at using Conditional Formatting (what DBY suggested)...

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select "only unique or duplicate values"
4. "duplicate" should be already selected - if not, pick that
5. Format Fill with your desired color

(btw, the COUNTIF() formula that DBY suggetsed, would have gone into CF as a "use formula" rule)

[mrbret]

I don't need anything to be counted or if anything is repeated. I just want to be able to add a formula that will highlight the names I specify in the formula.

Each name will only be listed once but could be in a different place.

Say you have the full alphabet from A - Z but I want to highlight in yellow only the letters D,H,R,M X.

[FDibbins]

Sorr, that was a typo, it meant to ask...

Do you mean you want to shade all cells with any of those names, or just cells that contain more that 1 instance of "bob" (for instance?)

[FDibbins]

1 way of doing this would be to have a list of names you want to highlight (I used J1:J3), then...

(having a problem posting, so posting a bit at a time)

[FDibbins]

1 way of doing this would be to have a list of names you want to highlight (I used J1:J3), then...

(having a problem posting, so posting a bit at a time)

[mrbret]

Sorr, that was a typo, it meant to ask...

Just to shade the cells with the names that would be specified and anything within that specified name list would stay as is.

Thanks very much

[FDibbins]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula

[FDibbins]

4. enter =COUNTIF($H$1:$H$8,$J$1:$J$3) format fill as needed

[Crisp Packet]

Hi

Like the attached!

[mrbret]

4. enter =COUNTIF($H$1:$H$8,$J$1:$J$3) format fill as needed

Im sorry, I dont understand: Wont the COUNTIF just count something and I don't know what the H and J bits have to do with it & where would I put the list of names I want it to highlight?

[mrbret]

Hi

Like the attached!

Not 100% what I was looking for but I think I could make that work.

Will the NAMES look for the exact match in the NAME LIST or a partial match (starting with, containing etc)?



I have got this to do what i want it to do.


Thanks very much everyone

[DBY]

Hello
There's obviously some confusion here, my CF formula would highlight a cell if for example that cell contained only the value 'Bob' or 'Steve' or 'Karen' etc. Conditional formatting relies on True of False statements to work. If COUNTIF is 0 then False, not zero True, so if say cell A2 = 'Bob' and Bob is in the names list then it returns a count greater than zero which equals True and the cell changes format, in this case yellow fill. However, if the cells contain not a single word but a string, ie: 'Bob, Karen, Steve', then my formula wouldn't work and would need to be amended.

DBY

[mrbret]

One more question related to the above info.

Is there a way to delete the cells instead of highlighting in yellow?

Thanks again

[Crisp Packet]

Hi mrbret

Probably code to delete the cells.
But in my sample file, change the conditional formatting from yellow fill to no fill and change the font colour to white.

[mrbret]

Hi mrbret

Probably code to delete the cells.
But in my sample file, change the conditional formatting from yellow fill to no fill and change the font colour to white.

That does work but if that info is copy/pasted into an email those blanked out cells can be copied and those names can be seen. Im trying to stop that