drumroll13

- Posts
- 4
- Registration date
- Tuesday July 14, 2015
- Last seen
- July 20, 2015

- Posts
- 2445
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 12, 2018

- Conditional formatting: Find text and highlight from other cell
- Excel conditional formatting contains text ✓ - Forum - Excel
- Conditional Formatting: Change fontcolor based on another cell ✓ - Forum - Excel
- Highlight cell if another cell contains any text ✓ - Forum - Excel
- Conditional format range if one cell contains certain text - Forum - Excel
- Conditional formatting based text in another cell - Forum - Excel

Best answer

TrowaD

- Posts
- 2445
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 12, 2018

Hi Drumroll,

I don't understand your question.

I imagine your table to look like this:

7 names in row 1, values in row 2 and 3 (in your case 67) and a total value row in row 4.

Now how can there be more instance of a single name?

Where am I taking a wrong turn?

Best regards,

Trowa

I don't understand your question.

I imagine your table to look like this:

Name 1 Name 2 Name 3 Name 4 Name 5 Name 6 Name 7 1 2 3 4 5 6 7 8 9 10 11 12 13 14 9 11 13 15 17 19 21

7 names in row 1, values in row 2 and 3 (in your case 67) and a total value row in row 4.

Now how can there be more instance of a single name?

Where am I taking a wrong turn?

Best regards,

Trowa

TrowaD

- Posts
- 2445
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 12, 2018

Hi Drumroll,

Just for future reference, let me show you a clear way of showing a table:

First paste you table into your message, select the table, click on the arror-down button next to the <> symbol, click on the "c" as shown in the below pic:

Click on "Preview" to check how your message would look like if you click "Submit".

Unfortunately your query is still unclear to me.

You say Column A has all the possible names. If you give all the names a different colour, then I could wright a code for you to look up the names throughout the sheet to apply the same colour.

Is that what you are after?

Also consider uploading your file using a free file sharing site like www.speedyshare.com or ge.tt and then post back the download link. Careful with sensitive information. This might give better insight as to what you want to achieve.

Best regards,

Trowa

Just for future reference, let me show you a clear way of showing a table:

First paste you table into your message, select the table, click on the arror-down button next to the <> symbol, click on the "c" as shown in the below pic:

Click on "Preview" to check how your message would look like if you click "Submit".

Unfortunately your query is still unclear to me.

You say Column A has all the possible names. If you give all the names a different colour, then I could wright a code for you to look up the names throughout the sheet to apply the same colour.

Is that what you are after?

Also consider uploading your file using a free file sharing site like www.speedyshare.com or ge.tt and then post back the download link. Careful with sensitive information. This might give better insight as to what you want to achieve.

Best regards,

Trowa

TrowaD

- Posts
- 2445
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 12, 2018

Hi Drumroll,

So for H4 on the Projects sheet, you want to check the hours per month on the Hour Calculations sheet and based on its value, change the colour to either green, yellow or red.

So with:

VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)

we retrieve the hours per month value.

When this value is bigger then 136 you want to change the cell to red. So the first conditional formatting rule for H4 on the Projects sheet will be:

=VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)>136 format cell as red.

Now do the same for the green and yellow colours.

Then drag H4 down as far as needed and click on the small square providing you with the option to "apply format only". This instead of applying format to each cell individually.

Does this yield the desired result?

Best regards,

Trowa

So for H4 on the Projects sheet, you want to check the hours per month on the Hour Calculations sheet and based on its value, change the colour to either green, yellow or red.

So with:

VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)

we retrieve the hours per month value.

When this value is bigger then 136 you want to change the cell to red. So the first conditional formatting rule for H4 on the Projects sheet will be:

=VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)>136 format cell as red.

Now do the same for the green and yellow colours.

Then drag H4 down as far as needed and click on the small square providing you with the option to "apply format only". This instead of applying format to each cell individually.

Does this yield the desired result?

Best regards,

Trowa

drumroll13

- Posts
- 4
- Registration date
- Tuesday July 14, 2015
- Last seen
- July 20, 2015

Yes it does yield my desired result and works much cleaner than my approach. Thank you for your help Trowa. VLOOKUP does exactly what need.Just to confirm, for the yellow, it should look like this

=AND(VLOOKUP($H5,'Hours Calculations'!$A$4:$B$36,2,FALSE)>120,VLOOKUP($H5,'Hours Calculations'!$A$4:$B$36,2,FALSE)<=136)

because there are two requirements, greater than 120 and less than or equal to 136

=AND(VLOOKUP($H5,'Hours Calculations'!$A$4:$B$36,2,FALSE)>120,VLOOKUP($H5,'Hours Calculations'!$A$4:$B$36,2,FALSE)<=136)

because there are two requirements, greater than 120 and less than or equal to 136

TrowaD

- Posts
- 2445
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 12, 2018

- Posts
- 4
- Registration date
- Tuesday July 14, 2015
- Last seen
- July 20, 2015

Hi Drumroll,

Great and you are right about the yellow, but you probably figured that out by trying.

Glad to be of help.

Best regards,

Trowa

Great and you are right about the yellow, but you probably figured that out by trying.

Glad to be of help.

Best regards,

Trowa

(sorry again i don't know how to make a table like you did)

staff hours staff hours

project 1 john 4 jill 2

project 2 greg 10 john 10

project 3 jill 3

etc.

so I have John twice and jill twice and jill should be green while john should be yellow for example

=AND(H4=$A$4,$B$4<=120) highlight green

=AND(H4=$A$4,$B$4>120,$B$4<=136) highlight yellow

=AND(H4=$A$4,$B$4>136) highlight red

The A column has all the possible names and the B column has the associated total value. H4 in this case doesn't have the dollar signs as I plan to use format painter on all the other cells once I am done with the first cell. So as I go through I will be changing the A# and B# for each person as I make more rules.

If there is another easier or faster method of doing this, please let me know, I would greatly appreciate not inputting 93 more rules