Conditional formatting dependent on information in another cell

Question

I have a spreadsheet created that I'd like to be able to do conditional formatting, but the coding is dependent on 2 criteria. For example, in the first column, there's a number 1 through 7, and there are 3 ranges for a percentage of completion. The percentage of completion changes whether it's associated with number 1, 2, 3, etc. Is there a way that I can color code based on a grid like that? Thank you!

Here's the grid for reference:

Session	Red	Yellow	Green
1	0-25%	26%-99%	100%
2	0-45%	46%-99%	100%

Answer 1

Hi @Carolyn Stamey,

Based on your sharing, it's absolutely doable with conditional formatting. With your reference grid, I assume that your Green is always 100%, Yellow is everything between Red and 100% and the remaining is Red. So, you just need the Red cutoff for each session.

First, I create a sample data and add an additional Red cutoff in column J

Now select the % cell (in my test sheet is B2:B15) and create 3 conditional formatting rules using "Use a formula to determine which cell to format" with these formulas:

• Red fill rule: =$B2 <= XLOOKUP($A2,$F$2:$F$8,$J$2:$J$8)
• Yellow rule: =AND($B2>XLOOKUP($A2,$F$2:$F$8,$J$2:$J$8),$B2<100%)
• Green rule: =$B2=100%

The outcome will be like this:

Hope this will be helpful with your concern. Please try the step and let me know if it works with your request. If not, please notify me and we can work together to find a solution.

If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you. 

Thank you for your patience and understanding. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly 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. 

Answer 2

Yes. Use a conditional formatting rule with a formula that tests both the session number and the completion percentage.

Assume:

• Column A = Session (1–7)
• Column B = % Complete (as a real percentage, e.g., 0.25 for 25%)
• Data starts in row 2

Example for Session 1 with ranges:

• Red: 0–25%
• Yellow: 26–99%
• Green: 100%

Select the cells in column B (for example B2:B100), then create three separate rules using Use a formula to determine which cells to format.

1. Red rule (Session 1, 0–25%)

=AND($A2=1,$B2>=0,$B2<=0.25)

Apply red fill.

2. Yellow rule (Session 1, 26–99%)

=AND($A2=1,$B2>0.25,$B2<1)

Apply yellow fill.

3. Green rule (Session 1, 100%)

=AND($A2=1,$B2=1)

Apply green fill.

For Session 2 (with different ranges, e.g., 0–45%, 46–99%, 100%), repeat with $A2=2 and adjusted percentage limits, for example:

• Red:

=AND($A2=2,$B2>=0,$B2<=0.45)

• Yellow:

=AND($A2=2,$B2>0.45,$B2<1)

• Green:

=AND($A2=2,$B2=1)

Repeat for sessions 3–7 with their own thresholds. The key points:

• Use $A2 so the rule always checks the session in column A of the same row.
• Use $B2 so the rule checks the percentage in column B of the same row.
• Ensure the formula returns TRUE/FALSE for each row, as required by conditional formatting.

If the percentages are stored as whole numbers (e.g., 25 instead of 0.25), adjust the limits accordingly (e.g., use 25 instead of 0.25, 100 instead of 1).

---

References:

• Use conditional formatting to highlight information in Excel