Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Is it possible to format the fill color of a cell to equal the fill color of another cell?

Copper Contributor

I have a spreadsheet with with columns conditionally formatted to fill (highlight) based upon the text of another cell. 

For example, the formula in C3 is '=$E$2', and if E2 contains "STS" the Conditional Formatting will Highlight C3 Red, or if E2 contains "SS", Conditional Formatting will Highlight C3 Yellow, etc. (with 12 text conditions equaling 12 different colors.)

I would like B2 (which contains an "@" text) to match the Highlighted color of C3.

Is that possible?  If so, how?  I've spent hours trying to figure this out and you all are my last hope.

Thank you in advance for any guidance.

11 Replies

@MrsJohnson 

Select B2.

 

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula =$E$2="STS"

Click Format...

Activate the Fill tab.

Select red.

Click OK twice.

 

Repeat these steps, but with =$E$2="SS" and yellow as fill color.

Etc. etc.

Hi Hans!!! I've already created the Conditional Formatting based upon the Text however, I'd like the adjacent Cells color to change when C3 changes (based upon the E2 content). So when C3 changes to Yellow, I'd like for B3 to change to Yellow too. I hope that makes sense. :)

@MrsJohnson 

You'll have to create as many rules for B3 as for C3 - as described in my previous reply.

@MrsJohnson 

 

This is disappointing...   I have been looking for the exact same answer for weeks! Unfortunately, the responder was not understanding the problem, therefore this is still unanswered.  I so hope the correct answer emerges.  I thought for sure that this would be a simple solution, but no.... It's a mystical Quest.

     Can anyone find the answer!?!?

@TemporaryUser314 

I assume that I was the responder.

Can you describe your problem in detail? Thanks in advance.

So I am creating a schedule from scratch. The idea is that in column A, the start time for a 15 minute break is found. ie, 10:00 am, we'll call this A5. This has been conditioned to highlight a cell within a table, in a cell valued at 10:00 am, we'll call this J5. This works...
The table is incremented in 5 minute intervals. The next 3 adjacent cells are, you guessed it, 10:05 am, 10:10 am, and 10:15 am. I'm trying to get those following 3 cells to highlight as well BECAUSE the cell value 10:00 am IS highlighted through CF. If it were any other time of day, also a value found in column A, the next 3 cells (or a combined 15 minute time slot) is highlighted. I can't just change the increments to 15 minute intervals because at some point only 10 minutes will be highlighted (or two 5 minute value cells.) I would rather not have to manually highlight the cells that follow J5 as the values found in column A will change. (A5 could hold a value of 10:15 am) If there is an easier solution, I'm open to suggestions.
Using a template is out of the question...

@TemporaryUser314 

Thanks. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

I'm sorry, but I am not able to do that. The question is, can I use CF (or another method) to highlight J16 BECAUSE J15 was highlighted through CF?
OR
Highlight [J15(+2 cells to the right)] without having to do it manually?

@TemporaryUser314 

It can probably be done, but without seeing a copy of the worksheet and the conditional formatting rules it's impossible to  know for sure.

the answer is most likely yes. You can highlight multiple cells the same way when using CF and using a custom rule. Just make the Applied To range cover each cell you want formatted that way and then create the formula so that the conditional is using ABSOLUTE references ($) accordingly. So if J15 has a rule =(E15="abc") then change the rule to include J16 in the Applied To range and change the formula to =(E$15="abc") so that when excel moves from J15 to J16 the row in E15 wont change because of the $ (of course you could also lock the column and use $E$15)
I apologize,
(Highlight [J15(+2 cells to the right)] without having to do it manually?)
The 2 cells to the right would be K15 and L15.
Maybe another way of saying it would be, I want to highlight (or condition) the 2 cells to the right of the pre-conditioned cell, J15.
If the cell in column A were to trigger a different cell, say R15, then it would instead highlight S15 and T15 respectively.
On a timeline it would simply look like the next 15 minutes were blocked out.