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

How to get cells to copy other cells on colour alone?

Copper Contributor

My exact situation: 

 

A1 is conditional formatted to turn green when text appears “work” 

 

I wish for A2 to turn green just because A1 did WITHOUT the text following or being needed 

 

it seems vba is needed but I’ve never used vba

 

any help is highly appreciated as I’ve been unable to solve this for a while now 

3 Replies
NO VBA needed. What you need is to use Conditional Formatting and select custom formula.
Select the range A2 then in the custom formula you type =(A1="work") and then set the formatting.
You can highlight the whole column by selecting A1:A100 and then use the formula =(A$1="work") because using the $ means every cell in that range is compared to row 1

@mtarler thank you, I had a Reddit post give a similar answer and it gave what I wanted

 

As a follow up do you know if it’s possible to get a sum number by colour alone without vba? 

 

It’s the last piece for me, to get an automated tally of the scores of each colour alone in top right 

 

thanks :folded_hands:

You can't use sheet formulas to differentiate cell colors BUT since the colors are based on conditional formatting you can certainly create a formula that uses that same logic to find the sum.