Forum Discussion

DanT-N's avatar
DanT-N
Copper Contributor
Jan 10, 2023

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

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 

  • mtarler's avatar
    mtarler
    Silver Contributor
    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
    • DanT-N's avatar
      DanT-N
      Copper Contributor

      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 🙏

      • mtarler's avatar
        mtarler
        Silver Contributor
        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.

Resources