How do I high light a cell based on the value in another cell

Hi, DaveW2020

Thank you for using Microsoft products and posting them in the community.

To highlight a cell in Excel based on the value of another cell, you can use conditional formatting. Specifically, if you want to highlight a cell in column K based on a value in column H, you can follow these steps:

1. Select the range of cells in column K to which you wish to apply conditional formatting. For example, select K3:K1000.
2. In the Excel menu, select the "Start" tab, and then click "Conditional Formatting".
3. Select New Rule.
4. In the New Rule window, select "use the formula to determine the format of the cell".
5. In the formula box, enter the following formula:

= 𝐻3 = "the value you want to match"

For example, if you want to highlight cells in column K when the value in column H is "Yes", the formula should be:

=𝐻3="𝑌𝑒𝑠"

6. Click on the "Format" button and set the format you want (e.g. fill color).
7. Click "OK" to complete the setup.

Note that the cell reference H3 in the formula is a relative reference, which means that it will be automatically adjusted according to the range you have selected. If you choose K3:K1000, then the formula will automatically apply to each cell in column K and check the corresponding cell value in column H.

The following is a specific example:

1. Select K3:K1000.
2. Click "Conditional Format" -> "New Rule".
3. Select "Use the formula to determine the cell to be formatted".
4. Enter the formula:

=𝐻3="𝑌𝑒𝑠"

5. Click on "Format" and select a fill color (e.g., green).
6. Click "OK".

This way, when a cell in column H has a value of "Yes", the corresponding cell in column K will be highlighted.

If you wish to highlight cells based on more complex conditions, you can adjust the formula as needed. For example, if you wish to highlight cells in column K when the value in column H is greater than 10, the formula could be:

=𝐻3>10

Use conditional formatting to highlight information in Excel - Microsoft Support

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Hi, DaveW2020

Understand that you want to highlight the matching cell in column K based on the last value in column H. We can accomplish this by following these steps:

1. Determine the last value in column H.
2. Use conditional formatting to highlight the cell in column K that matches the last value in column H.
3. Assuming that the data in column H starts at H1, then we can use the following steps:
4. Select the range of cells in column K to which you want to apply conditional formatting, for example K3:K1000.
5. In the Excel menu, select the Start tab and click on Conditional Formatting.
6. Select New Rule.
7. In the New Rule window, select "use the formula to determine the cell to be formatted".
8. In the formula box, enter the following formula: = K3 = INDEX (H: H, COUNTA (H: H))

The effect of the formula is:

INDEX(H:H,COUNTA(H:H)) returns the last value in the H column.

=K3=INDEX(H:H,COUNTA(H:H)) Check whether the cell in column K is equal to the last value in column H.

Click the "Format" button to set the format you want (e.g. fill color yellow).

Click OK to complete the setup.

Thus, when the value in column K matches the last value in column H, the corresponding cell in column K will be highlighted.

As shown in the figure:

The effect is as follows:

Finally, if you find me helpful in this thread, you can mark this thread with a yes or no, this will also help other users to quickly locate this thread, thank you for your cooperation.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Hi, DaveW2020

Thank you for using Microsoft products and posting them in the community.

To highlight a cell in Excel based on the value of another cell, you can use conditional formatting. Specifically, if you want to highlight a cell in column K based on a value in column H, you can follow these steps:

1. Select the range of cells in column K to which you wish to apply conditional formatting. For example, select K3:K1000.
2. In the Excel menu, select the "Start" tab, and then click "Conditional Formatting".
3. Select New Rule.
4. In the New Rule window, select "use the formula to determine the format of the cell".
5. In the formula box, enter the following formula:

= 𝐻3 = "the value you want to match"

For example, if you want to highlight cells in column K when the value in column H is "Yes", the formula should be:

=𝐻3="𝑌𝑒𝑠"

6. Click on the "Format" button and set the format you want (e.g. fill color).
7. Click "OK" to complete the setup.

Note that the cell reference H3 in the formula is a relative reference, which means that it will be automatically adjusted according to the range you have selected. If you choose K3:K1000, then the formula will automatically apply to each cell in column K and check the corresponding cell value in column H.

The following is a specific example:

1. Select K3:K1000.
2. Click "Conditional Format" -> "New Rule".
3. Select "Use the formula to determine the cell to be formatted".
4. Enter the formula:

=𝐻3="𝑌𝑒𝑠"

5. Click on "Format" and select a fill color (e.g., green).
6. Click "OK".

This way, when a cell in column H has a value of "Yes", the corresponding cell in column K will be highlighted.

If you wish to highlight cells based on more complex conditions, you can adjust the formula as needed. For example, if you wish to highlight cells in column K when the value in column H is greater than 10, the formula could be:

=𝐻3>10

Use conditional formatting to highlight information in Excel - Microsoft Support

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Hi Aiden,

Thank you for getting back to me.

In your reply on the first part, "5. In the formula box, enter the following formula:

= 𝐻3 = "the value you want to match"".

What I need is the last value in column H and then have that value high-lighted in column "K"

Example: the last number in column A is 7, then lets say column K is numbered 1 to 10+, then the cell that has the value 7, the background turns yellow.

The column A will have a different number stored in the next the next round, A1=7, A2= 19, A3=4 and so on. Then the value that match the value in column K will change with each new round based on column A newest value.

I hope that the above is clear on what I need.

Thanks for your help.