Change Coulour if gender is female

everblazing

Board Regular
Joined
Sep 18, 2015
Messages
154
Hi

would appreciate any assistance here. I have a cell in sheet Fig 2 that has multiple names. this cell brings names based on a macro from Master_Data sheet. I would like to be able to know who is male and who is female. In Master_Data I have a column which specifies female or male, Column D. Names is Column B.

would appreciate any help if there is a conditional formatting that has if cell A2 contains a name that is female in column D then change to blue.

Regards
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,531
Create a new rule using a formula to determine which cells to format. Then add a VLOOKUP (or any other lookup formula) as the rule.

If you want to format several columns at the same time use a fixed reference for the lookup value. Also remember to use exact match with your names.
 

everblazing

Board Regular
Joined
Sep 18, 2015
Messages
154
Hi Misca,

thank you for your reply, In cell B2 Sheet Fig 2 I have multiple names. unfortunately its not working. potentially because i have multiple names in 1 cell.

Appreciate your help.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,674
Hi,

You do need to have each name in a cell ...

If your constraint is to keep your current sheet as it is ....

Then you can replicate on a new tab you current sheet ... and generate clean cells ...

Hope this will help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
As long as A2 only has one name try
=AND(A2<>"",INDEX(Master_Data!$D$2:$D$10,MATCH("*"&A2&"*",Master_Data!$A$2:$A$10,0))="F")
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,531
If every name in the row are the same gender you can lookup just the first name and colorcode the row based on that only. To be able to do this within the lookup formula you'd have to know the delimiter. And make sure all the rows with multiple names are constructed the same way. I can't actually recommend this way but it can be done if you really must.

The recommended way would be to have only one person per row and if you must group them, have a different column for the group they belong in. If a person can belong in several groups, there should be a row for that person in each group he/she belongs in.
 

Forum statistics

Threads
1,078,515
Messages
5,340,863
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top