Color coding a cell based on the contents of a different cell

Question

Color coding a cell based on the contents of a different cell

Dee Cee 20

User's image

Barry Schwarz 3,746 Reputation points

2025-03-19T01:43:32.93+00:00

If I understand correctly, you want cell B2 on sheet 1 to have the same color as cell C2 on sheet 2 and similarly for cells B3:B7 on sheet 1.

Question 1: How is column C on sheet 2 populated? Did someone manually look at B2, decide 500 is healthy, and manually set the value of C2? Or is there a set of thresholds that distinguish between the three statuses? (Are there only three?)

Question 2: After the value in Cx has been assigned, how was the formatting applied? Is there a set of Conditional Formatting formulas that says Healthy is green, etc? Or is there a set of cells we don't see that has the statuses with the formatting already applied and these are just copied to column C?

Question 3: How was column D on sheet 1 generated? It appears as if the problem has already been solved for that column and something similar might work for column B.
Dee Cee 20 Reputation points

2025-03-19T17:34:23.18+00:00
Hi Barry

Question 1: How is column C on sheet 2 populated? Did someone manually look at B2, decide 500 is healthy, and manually set the value of C2? Or is there a set of thresholds that distinguish between the three statuses? (Are there only three?)

Column C on sheet 2 is from a report that determines if the stock code is healthy based on the inventory levels. I have conditional formatting on that column to show green for healthy inventory levels, yellow for low stock and red for out of stock.

Column B on sheet 1 is a vlookup of column B on sheet 2. I only put the colors on sheet 2 to show what I want on sheet 1.

I want Column B on sheet 1 to reflect the color of Column C sheet 2. No where on sheet 1 will I have a column that says "healthy" or "low stock" or "out of stock" but I want the color in column B on sheet 1 to show the status - a visual cue for me what the stocking level is.

Question 2: After the value in Cx has been assigned, how was the formatting applied? Is there a set of Conditional Formatting formulas that says Healthy is green, etc? Or is there a set of cells we don't see that has the statuses with the formatting already applied and these are just copied to column C?

Answered under question 1

Question 3: How was column D on sheet 1 generated? It appears as if the problem has already been solved for that column and something similar might work for column B.

Answered under question 1

2 answers

Your answer

Barry Schwarz 3,746 Reputation points

2025-03-19T01:43:32.93+00:00

If I understand correctly, you want cell B2 on sheet 1 to have the same color as cell C2 on sheet 2 and similarly for cells B3:B7 on sheet 1.

Question 1: How is column C on sheet 2 populated? Did someone manually look at B2, decide 500 is healthy, and manually set the value of C2? Or is there a set of thresholds that distinguish between the three statuses? (Are there only three?)

Question 2: After the value in Cx has been assigned, how was the formatting applied? Is there a set of Conditional Formatting formulas that says Healthy is green, etc? Or is there a set of cells we don't see that has the statuses with the formatting already applied and these are just copied to column C?

Question 3: How was column D on sheet 1 generated? It appears as if the problem has already been solved for that column and something similar might work for column B.
Dee Cee 20 Reputation points

2025-03-19T17:34:23.18+00:00

Hi Barry

Question 1: How is column C on sheet 2 populated? Did someone manually look at B2, decide 500 is healthy, and manually set the value of C2? Or is there a set of thresholds that distinguish between the three statuses? (Are there only three?)

Column C on sheet 2 is from a report that determines if the stock code is healthy based on the inventory levels. I have conditional formatting on that column to show green for healthy inventory levels, yellow for low stock and red for out of stock.

Column B on sheet 1 is a vlookup of column B on sheet 2. I only put the colors on sheet 2 to show what I want on sheet 1.

I want Column B on sheet 1 to reflect the color of Column C sheet 2. No where on sheet 1 will I have a column that says "healthy" or "low stock" or "out of stock" but I want the color in column B on sheet 1 to show the status - a visual cue for me what the stocking level is.

Question 2: After the value in Cx has been assigned, how was the formatting applied? Is there a set of Conditional Formatting formulas that says Healthy is green, etc? Or is there a set of cells we don't see that has the statuses with the formatting already applied and these are just copied to column C?

Answered under question 1

Question 3: How was column D on sheet 1 generated? It appears as if the problem has already been solved for that column and something similar might work for column B.

Answered under question 1

Answer 1

Emi Zhang-MSFT 30,046 Microsoft External Staff

Hi.

Can you provide the range of conditions corresponding to different colors?

I created a sample about your requirement:

Red color: =0 Out of stock

Yellow color: >=1, <=400 Low inventory

Green color: >400 Healthy

Select the range C2:C10, go to Home- Conditional Formatting- select New Rule...
Select Use a formula to determine which cells to format- then enter the formula for different color and set the cell formatting: Red: =B2=0
Yellow: =AND(B2>=1,B2<=400) Green: =B2>400
Then enter the formula in cells:
=IF(B2=0,"Out of stock",IF(AND(B2>=1,B2<=400),"Low inventory",IF(B2>400,"Healthy","")))

Hope the steps will help.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Dee Cee 20 Reputation points

2025-03-19T17:37:27.24+00:00

Hi Emi - I have conditional formatting set up already on sheet 2. What I want is for column c on sheet 1 to show the color of green for healthy, yellow for low inventory and red for out of stock. Column C on sheet 1 is a vlookup - if I can put conditional formatting on column c of sheet 1 that refers to column C of sheet 1, that would be great, but I don't know how to do that.

Answer 2

If I understand correctly, the colors in column D on sheet 2 are thee just so we know what colors you want to see in column B on sheet1. The values in sheet 2, including the status in column D come from the report. You want the colors in column B on sheet 1 to provide a quick indication of the status on sheet2.

In your sample data, each stock code on sheet 1 is in the same row as that value on sheet 2. In that case, the following conditional formatting formula applied to sheet 1 column B will allow you to assign a green fill those stock codes that the report marked healthy in sheet 2:

=INDIRECT(ADDRESS(ROW(),4,,,"sheet2"))="Healthy"

Creating two additional formulas, each with an appropriate quoted status, will let you assign the red and orange fills in the same way.

If the stock codes are not in the same rows on the two sheets, the first argument of the ADDRESS function becomes a little more complex but the concept is the same.

Share via

Color coding a cell based on the contents of a different cell

2 answers

Your answer